MySQL自连接结合GROUP BY与SUM 分组统计各队伍总得分

你可以按以下逻辑实现需求，核心是先基于原表提取全量队伍作为统计基准，通过自连接关联对局记录，再结合GROUP BY分组和SUM聚合计算各队总得分：

基础数据逻辑 #

game1表共3条示例对局记录，对应对阵得分如下：

• team1 vs team2：比分10:5
• team1 vs team3：比分10:2
• team2 vs team3：比分11:5

实现SQL（符合自连接+分组聚合要求） #

SELECT
    base.player,
    SUM(
        CASE
            WHEN base.player = g.player1 THEN g.result_1
            WHEN base.player = g.player2 THEN g.result_2
            ELSE 0
        END
    ) AS result
FROM (
    -- 从原表提取所有不重复队伍作为统计基准，和原表形成自连接
    SELECT player1 AS player FROM game1
    UNION
    SELECT player2 AS player FROM game1
) base
INNER JOIN game1 g 
    ON base.player = g.player1 OR base.player = g.player2
GROUP BY base.player;

执行结果 #

查询返回player（队伍标识）、result（总得分）两个字段，结果和预期完全匹配：

• team1：总得分20（两场累计10+10）
• team2：总得分16（两场累计5+11）
• team3：总得分7（两场累计5+2）

大数据量场景下可以用更高效的UNION ALL写法，直接打平主客队得分后聚合，逻辑更简洁性能更好：

SELECT player, SUM(score) AS result
FROM (
    SELECT player1 AS player, result_1 AS score FROM game1
    UNION ALL
    SELECT player2 AS player, result_2 AS score FROM game1
) t
GROUP BY player;

内容的提问来源于stack exchange，提问作者Jinyeong