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

Question

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

链接到我的 SQL fiddle : 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;

上面的连接查询只返回来自 t1 的玩家集合，而我希望它返回来自 t2 的所有玩家。

我想要:玩家编号|姓名|胜|所有玩家参加的总比赛数，如果他们没有获胜，则获胜列中为 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

Answer 1

我认为查询可以简化为:

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 Fiddle Demo

关键是你要在match表上进行left join。剩下的就是简单的条件聚合。