sql - 加入多个选择查询 SQL

Question

我有两个具有不同 where 子句的查询，我需要连接这两个查询以获得单个结果表。

第一个查询:

SELECT 
    players.id,player_name,count(matches.winner) as wintotal 
FROM 
    matches, players 
WHERE 
    matches.winner = players.id 
GROUP BY 
    players.id;

它返回这些结果:

 id | player_name |      wintotal 
 45 | Vijay       |        2
 43 | Rahul       |        1
 46 | Shinoy      |        1
 48 | Sunil       |        2
 44 | Adarsh      |        4

第二个查询:

SELECT 
    players.id, player_name, count(*) as totalgames 
FROM 
    matches, players 
WHERE 
    matches.winner = players.id or matches.loser = players.id 
GROUP BY 
    players.id;

返回:

 id | player_name |         Total Matches
 45 | Vijay       |          4
 43 | Rahul       |          2
 46 | Shinoy      |          4
 48 | Sunil       |          2
 44 | Adarsh      |          6
 47 | Pranjal     |          2

在这两个查询中，两个查询的 where 子句不同，最后一列也不同。

• 第一个查询返回玩家的总胜利数
• 第二个查询返回玩家参加的总比赛数

如何连接两个查询以在单个查询中同时获得列获胜数和总匹配数？

预期输出:

 id | player_name |         Total Matches | wintotal
 45 | Vijay       |          4            |          2
 43 | Rahul       |          2            |          1
 46 | Shinoy      |          4            |          1
 48 | Sunil       |          2            |          2
 44 | Adarsh      |          6            |          4
 47 | Pranjal     |          2            |          0

谢谢

Answer 1

尝试:

select players.id,
       player_name,
       count(case when matches.winner=players.id then 1 end) as wintotal ,
       count(*) as totalgames
from matches
join players 
on matches.winner=players.id or matches.loser=players.id
group by players.id, 
         player_name;