将同一表的多个列上的条件计数相加

该查询并不像乍看起来那样简单。最短的查询字符串不一定会产生最佳性能。这应该 尽可能快 ，为此应尽可能短：

SELECt p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lostFROM  (   SELECt loser_id AS player_id, count(*) AS ct   FROM   match   WHERe  winner_id = 1  -- your player_id here   GROUP  BY 1-- positional reference (not your player_id)   ) wFULL JOIN (   SELECt winner_id AS player_id, count(*) AS ct   FROM   match   WHERe  loser_id = 1   -- your player_id here   GROUP  BY 1   ) l USING (player_id)JOIN   player p USING (player_id)ORDER  BY 1;

结果完全符合要求：

username | won | lost---------+-----+-----alice    | 3   | 2bob      | 1   | 0mary     | 2   | 1

SQL Fiddle- 具有更多显示的测试数据！

关键功能是

FULL [OUTER]JOIN

USING

player_id

在那之后，用一个

JOIN

player

COALESCE

使用两个多列 索引 甚至会更快：

CREATE INDEX idx_winner on match (winner_id, loser_id);CREATE INDEX idx_loser  on match (loser_id, winner_id);

仅 当您从中获得仅索引扫描时。然后Postgres甚至根本不访问

match

使用两

integer

• 复合索引对第一个字段的查询是否也有用？

您可以运行相关的子查询，例如@Giorgi建议，只要工作
正常即可 ：

SELECt *FROM  (   SELECt username       , (SELECT count(*) FROM match          WHERe  loser_id  = p.player_id          AND    winner_id = 1) AS won       , (SELECt count(*) FROM match          WHERe  winner_id = p.player_id          AND    loser_id  = 1) AS lost   FROM   player p   WHERe  player_id <> 1   ) subWHERe (won > 0 OR lost > 0)ORDER  BY username;

适用于 小型
桌子，但不能扩展。这需要

player

match

EXPLAINANALYZE