基于Games表编写SQL查询计算玩家总得分并输出指定结果

现有Games表结构及数据如下：

需要编写SQL查询语句，输出如下结果：

解决方案 #

可以通过UNION ALL将两个玩家列的分数数据合并，再分组求和并排序：

SELECT Player, SUM(Score) AS TotalScore
FROM (
    SELECT Player1 AS Player, ScorePlayer1 AS Score FROM Games
    UNION ALL
    SELECT Player2 AS Player, ScorePlayer2 AS Score FROM Games
) AS PlayerScores
GROUP BY Player
ORDER BY TotalScore DESC;

该查询先将每局游戏中两个玩家的得分拆分为独立记录，再按玩家名称分组计算总分，最后按总分从高到低排序，得到目标结果。

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