SQL如何从胜负记录表统计每位玩家的胜场与负场数量

实现方案 #

这里提供两种兼容绝大多数数据库的实现方式：

方法1：拆分行后聚合（逻辑最直观，全版本兼容） #

将每场比赛拆为胜者、负者两条独立记录，分别标记胜/负计数后分组求和即可：

SELECT 
  player_name,
  SUM(is_win) AS number_wins,
  SUM(is_loss) AS number_losses
FROM (
  SELECT winner_name AS player_name, 1 AS is_win, 0 AS is_loss FROM my_table
  UNION ALL
  SELECT loser_name AS player_name, 0 AS is_win, 1 AS is_loss FROM my_table
) AS player_records
GROUP BY player_name
ORDER BY player_name;

方法2：分维度统计后关联（适合大数据量场景） #

先分别统计胜场、负场的聚合结果，再用所有玩家的全集关联两个统计结果，避免拆行带来的临时数据量膨胀：

WITH all_players AS (
  -- 获取所有参赛玩家的全集
  SELECT winner_name AS player_name FROM my_table
  UNION
  SELECT loser_name AS player_name FROM my_table
),
win_stats AS (
  -- 统计胜场
  SELECT winner_name AS player_name, COUNT(*) AS number_wins FROM my_table GROUP BY winner_name
),
loss_stats AS (
  -- 统计负场
  SELECT loser_name AS player_name, COUNT(*) AS number_losses FROM my_table GROUP BY loser_name
)
SELECT 
  p.player_name,
  COALESCE(w.number_wins, 0) AS number_wins,
  COALESCE(l.number_losses, 0) AS number_losses
FROM all_players p
LEFT JOIN win_stats w ON p.player_name = w.player_name
LEFT JOIN loss_stats l ON p.player_name = l.player_name
ORDER BY p.player_name;

如果使用不支持CTE的低版本数据库，将上述CTE部分替换为对应子查询即可正常运行。两种方案返回的结果都和你要求的输出结构完全一致。

内容的提问来源于stack exchange，提问作者B. Bachir