sql - PostgreSQL-左连接，计数输出错误

我试图建立一个简单的表格来显示锦标赛的结果-我有以下结构：

CREATE TABLE players(
    id SERIAL PRIMARY KEY,
    name TEXT);

CREATE TABLE matches(
    id SERIAL PRIMARY KEY,
    player_one_id INTEGER REFERENCES players,
    player_two_id INTEGER REFERENCES players,
    winner_id INTEGER REFERENCES players);

我输入了一些测试数据，如下所示：

INSERT INTO players (name) VALUES ('Mike Jones');
INSERT INTO players (name) VALUES ('Albert Awesome');
INSERT INTO players (name) VALUES ('Sad Sally');
INSERT INTO players (name) VALUES ('Lonely Lenny');

INSERT INTO matches (player_one_id, player_two_id, winner_id) VALUES (1,2,1);
INSERT INTO matches (player_one_id, player_two_id, winner_id) VALUES (3,4,4);

我正在尝试执行一个查询，为每个玩家提供以下结果：
id，name，matched，matches，matches。
到目前为止，我有以下疑问：

SELECT players.id, players.name, count(matches.winner_id) as matches_won
                               , count(matches.id) as matches_played
    FROM players left join matches
    ON players.id = matches.winner_id
GROUP BY players.id
ORDER BY matches_won DESC

不幸的是，我得到的输出不正确，如下所示（每个玩家应该有1场比赛）：

 id |      name      | matches_won | matches_played
----+----------------+-------------+----------------
  4 | Lonely Lenny   |           1 |              1
  1 | Mike Jones     |           1 |              1
  2 | Albert Awesome |           0 |              0
  3 | Sad Sally      |           0 |              0
(4 rows)

现在，我知道这个错误输出的原因是因为加入了players.id=matches.winner\u id，但是，我的问题是：
只有一个左连接查询就可以得到这些结果吗？如果是，怎么做？如果可能的话，我想避免做多个查询。

最佳答案

对。首先，您需要了解count()只计算具有非空值的行数，因此您的两个计数应该相同。
要获得优胜者，请使用条件聚合：

SELECT p.id, p.name,
       sum(case when m.winner_id = p.id then 1 else 0 end) as matches_won,
       count(m.id) as matches_played
FROM players p left join
     matches m
     ON p.id in (m.player_one_id, m.player_two_id)
GROUP BY p.id
ORDER BY matches_won DESC;

您还需要修复join条件。你不能只参加赢家的比赛就指望得到所有比赛的次数。

关于sql - PostgreSQL-左连接，计数输出错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28478014/