此代码按预期工作，但我很长，让人毛骨悚然。

select p.name, p.played, w.won, l.lost from

(select users.name, count(games.name) as played
from users
inner join games on games.player_1_id = users.id
where games.winner_id > 0
group by users.name
union
select users.name, count(games.name) as played
from users
inner join games on games.player_2_id = users.id
where games.winner_id > 0
group by users.name) as p

inner join

(select users.name, count(games.name) as won
from users
inner join games on games.player_1_id = users.id
where games.winner_id = users.id
group by users.name
union
select users.name, count(games.name) as won
from users
inner join games on games.player_2_id = users.id
where games.winner_id = users.id
group by users.name) as w on p.name = w.name

inner join

(select users.name, count(games.name) as lost
from users
inner join games on games.player_1_id = users.id
where games.winner_id != users.id
group by users.name
union
select users.name, count(games.name) as lost
from users
inner join games on games.player_2_id = users.id
where games.winner_id != users.id
group by users.name) as l on l.name = p.name

正如你所看到的，它由3个部分重复检索：

球员的名字和游戏大打量
球员的名字和比赛，他们赢得的金额
球员的名字和比赛他们输掉的金额

而且每个也由两个部分组成：

球员的名字和比赛中，他们的身份参加PLAYER_1量
球员的名字和比赛中，他们的身份参加player_2量

这怎么可能被简化？

结果看起来像这样：

           name            | played | won | lost 
---------------------------+--------+-----+------
 player_a                  |      5 |   2 |    3
 player_b                  |      3 |   2 |    1
 player_c                  |      2 |   1 |    1

Answer 1:

由于这是对“长和爬行”的追求，查询可以大大缩短，但。即使在第9.3（或实际上任何版本）：

SELECT u.name
     , count(g.winner_id  > 0 OR NULL) AS played
     , count(g.winner_id  = u.id OR NULL) AS won
     , count(g.winner_id <> u.id OR NULL) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;

更多的解释：

对于绝对性能，是SUM更快或算？

在第9.4这可与（已经提到的像@Joe）新骨料筛选子句清洁。

SELECT u.name
     , count(*) FILTER (WHERE g.winner_id  > 0) AS played
     , count(*) FILTER (WHERE g.winner_id  = u.id) AS won
     , count(*) FILTER (WHERE g.winner_id <> u.id) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;

该手册
Postgres的维基
Depesz博客文章

Answer 2:

这是相关子查询可以简化逻辑的情况下：

select u.*, (played - won) as lost
from (select u.*,
             (select count(*)
              from games g
              where g.player_1_id = u.id or g.player_2_id = u.id
             ) as played,
             (select count(*)
              from games g
              where g.winner_id = u.id
             ) as won
      from users u
     ) u;

这假定不存在约束。

Answer 3:

select users.name, 
       count(case when games.winner_id > 0 
                  then games.name 
                  else null end) as played,
       count(case when games.winner_id = users.id 
                  then games.name 
                  else null end) as won,
       count(case when games.winner_id != users.id 
                  then games.name 
                  else null end) as lost
from users inner join games 
     on games.player_1_id = users.id or games.player_2_id = users.id
group by users.name;

文章来源: How can I simplify this game statistics query?