如何将这两个PSQL查询正确地连接在一起？

debugcn 发表于 Dev

操作操作

我正在尝试返回一个查询，该查询将显示玩家在游戏中玩了多少次以及他/她赢得了多少次。到目前为止，这是我在最后一个联接查询上遇到的问题

链接到我的SQL小提琴：http：//sqlfiddle.com/#！15 / 46b2a / 52

我有两个表：

CREATE TABLE player(
id serial PRIMARY KEY NOT NULL,
name varchar(255) NOT NULL
);

CREATE TABLE match(
id serial PRIMARY KEY,
winner serial REFERENCES player(id) NOT NULL,
loser serial REFERENCES player(id) NOT NULL CHECK (loser != winner)
);

CREATE SEQUENCE playerid_sequence 
start 1
increment 1;

CREATE SEQUENCE matchid_sequence
start 1
increment 1;

我用一些示例插入填充了表格：

--Player Insertion 
INSERT INTO player VALUES(nextval('playerid_sequence'), 'Kevin');
INSERT INTO player VALUES(nextval('playerid_sequence'), 'Dennis');
INSERT INTO player VALUES(nextval('playerid_sequence'), 'George');
INSERT INTO player VALUES(nextval('playerid_sequence'), 'Michael');

--Match Insertion
INSERT INTO match VALUES(nextval('matchid_sequence'), 1, 2);
INSERT INTO match VALUES(nextval('matchid_sequence'), 1, 3);
INSERT INTO match VALUES(nextval('matchid_sequence'), 1, 4);
INSERT INTO match VALUES(nextval('matchid_sequence'), 2, 3);

我创建了两个SQL查询：

--How many did a player win?
SELECT player.id, player.name, count(player.name) as wins FROM player, match 
WHERE player.id = match.winner GROUP by player.name, player.id ORDER BY wins DESC;

--How many matches did a particular player participate in?
SELECT player.id, player.name, count(player.name) as matches_played
FROM player, match
WHERE player.id = match.winner OR player.id = match.loser 
GROUP by player.name, player.id
ORDER by matches_played DESC;

我尝试加入他们：

SELECT 
* 
FROM
(SELECT player.id, player.name, count(player.name) as wins FROM player, match 
WHERE player.id = match.winner GROUP by player.name, player.id ORDER BY wins DESC) t1 
FULL OUTER JOIN
(SELECT player.id as id, count(player.name) as matches_played
FROM player, match
WHERE player.id = match.winner OR player.id = match.loser 
GROUP by player.id
ORDER by matches_played DESC) t2
ON t1.id = t2.id;

上面的join查询仅返回t1的一组玩家，而我希望它返回t2的所有玩家。

我想要：玩家ID | 名称| 胜| 所有玩家的总比赛数，如果未赢，则胜利列中为0。

我有：

 id name    wins    id  matches_played
 1  Kevin   3       1    3
 2  Dennis  1       2    2
 (null) (null)  (null)  4   1
 (null) (null)  (null)  3   2

斯坦

我认为查询可以简化为：

select p.id, p.name,
       count(case when m.winner = p.id then 'X' end) as wins,
       count(m.id) as matches_played
  from player p
  left join match m
    on m.winner = p.id or m.loser = p.id
 group by p.id, p.name;

SQL小提琴演示

关键是您要在match表上保留联接。剩下的就是简单的条件聚合。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。