如何使用SQL对多列按规则赋分求和并统计各队伍总积分

解决方案 #

核心思路是把三种得分场景的记录拆分开合并后分组求和，假设你的表名为match_records，可直接使用如下SQL：

SELECT 
  team AS Team,
  SUM(points) AS Points
FROM (
  -- 常规胜利加3分
  SELECT Winner_R AS team, 3 AS points FROM match_records WHERE Winner_R IS NOT NULL
  UNION ALL
  -- PV胜利加2分
  SELECT Winner_PV AS team, 2 AS points FROM match_records WHERE Winner_PV IS NOT NULL
  UNION ALL
  -- PV失败加1分
  SELECT Loser_PV AS team, 1 AS points FROM match_records WHERE Loser_PV IS NOT NULL
) AS all_score_records
GROUP BY team
ORDER BY Team;

如果你的实际表名和示例不一致，替换match_records为你自己的表名即可，执行结果完全符合你要求的输出格式。

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