如何简化这个游戏统计查询？

Question

如何简化这个游戏统计查询？

这段代码如预期的那样工作，但我觉得它很长，而且令人毛骨悚然。

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

如您所见，它由三个用于检索的重复部分组成：

• 玩家名称和他们玩的游戏数量
• 玩家的名字和他们赢得的游戏数量
• 玩家名称和他们输掉的游戏数量

每一部分还包括两个部分：

• 玩家名称和作为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

在这种情况下，相关子查询可以简化逻辑：select u.*, (played - won) as lostfrom (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;这假定没有任何联系。