如何简化此游戏统计查询？

Question

如何简化此游戏统计查询？这段代码按预期工作，但我很长很令人毛骨悚然。select p.name, p.played, w.won, l.lost from(select users.name, count(games.name) as playedfrom usersinner join games on games.player_1_id = users.idwhere games.winner_id > 0group by users.nameunionselect users.name, count(games.name) as playedfrom usersinner join games on games.player_2_id = users.idwhere games.winner_id > 0group by users.name) as pinner join(select users.name, count(games.name) as wonfrom usersinner join games on games.player_1_id = users.idwhere games.winner_id = users.idgroup by users.nameunionselect users.name, count(games.name) as wonfrom usersinner join games on games.player_2_id = users.idwhere games.winner_id = users.idgroup by users.name) as w on p.name = w.nameinner join(select users.name, count(games.name) as lostfrom usersinner join games on games.player_1_id = users.idwhere games.winner_id != users.idgroup by users.nameunionselect users.name, count(games.name) as lostfrom usersinner join games on games.player_2_id = users.idwhere games.winner_id != users.idgroup 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

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

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;

这假设没有联系。