多个聚合计数列 SQL

Question

我有一个表格，大致如下所示：

winner_name	loser_name
人 A	人 B
人 A	人 C
人 B	人 A
人 C	人 B

您需要使用UNION将名称透视到同一列中。然后你可以计算输赢的总和。

SELECT player_name, SUM(win) AS number_wins, SUM(loss) AS number_losses)
FROM (
SELECT winner_name AS player_name, 1 AS win, 0 AS loss
FROM my_table
UNION ALL
SELECT loser_name AS player_name, 0 AS win, 1 AS loss
FROM my_table
) AS x
GROUP BY player_name

由于每个聚合统计信息针对不同的组(一个用于winner_name，另一个用于loser_name)，因此它们不能在同一查询中计算，但每个查询可以单独运行，然后与JOIN组合。只需获取每个查询：

SELECT winner_name AS player, COUNT(loser_name) AS wins
FROM games 
GROUP BY winner_name
;
SELECT loser_name AS player, COUNT(winner) AS losses
FROM games
GROUP BY loser_name
;

并加入公共属性，即玩家名称：

SELECT gw.player, gw.wins, gl.losses
FROM (
SELECT winner_name AS player, COUNT(loser_name) AS wins
FROM games 
GROUP BY winner_name
) AS gw
JOIN (
SELECT loser_name AS player, COUNT(winner_name) AS losses
FROM games
GROUP BY loser_name
) AS gl
ON gl.player = gw.player
;

无论是使用并集还是联接，作为聚合统计信息基础的每个不同组都需要单独的子选择。