将国际象棋比赛结果转换为排行榜的技术实现咨询

我有一张存储国际象棋比赛结果的数据表：

其中result为枚举类型，定义如下：

• 0 = 白方胜利
• 1 = 黑方胜利
• 2 = 协议平局
• 3 = 将死平局（stalemate）
• 4 = 重复局面平局
• 5 = 五十回合规则平局
• 6 = 比赛中止

我希望按照玩家的胜利次数和平局次数从多到少排序，生成排行榜视图。请问是否有直接实现该视图的方法，或是需要调整现有数据的组织方式？

直接生成排行榜视图的方法（无需调整现有数据） #

可以通过SQL查询直接生成所需的排行榜视图，核心思路是拆分统计每个玩家作为白方、黑方时的胜负平数据，再聚合排序。以下是通用SQL示例（适配MySQL、PostgreSQL等主流数据库）：

WITH player_results AS (
    -- 统计白方玩家的胜负平
    SELECT
        player_white AS player,
        CASE WHEN result = 0 THEN 1 ELSE 0 END AS wins,
        CASE WHEN result IN (2,3,4,5) THEN 1 ELSE 0 END AS draws
    FROM chess_games
    WHERE result != 6 -- 排除比赛中止的无效场次
    UNION ALL
    -- 统计黑方玩家的胜负平
    SELECT
        player_black AS player,
        CASE WHEN result = 1 THEN 1 ELSE 0 END AS wins,
        CASE WHEN result IN (2,3,4,5) THEN 1 ELSE 0 END AS draws
    FROM chess_games
    WHERE result != 6
)
SELECT
    player,
    SUM(wins) AS total_wins,
    SUM(draws) AS total_draws,
    SUM(wins)*2 + SUM(draws) AS total_points -- 按国际象棋积分规则：胜2分，平1分
FROM player_results
GROUP BY player
ORDER BY total_wins DESC, total_draws DESC;

关键说明： #

1. 用UNION ALL合并白方、黑方的统计结果，确保每个玩家的所有有效场次都被计算
2. 通过CASE语句精准匹配胜负平的枚举值，平局包含2、3、4、5四类情况
3. 排序优先级：先按胜利次数降序，胜利次数相同则按平局次数降序
4. 如需简化积分规则，可自行调整total_points的计算逻辑

可选：调整数据组织方式优化查询效率 #

如果数据量较大且需要频繁查询排行榜，可以新增一张玩家统计汇总表（例如player_stats），结构如下：

之后通过两种方式同步数据：

1. 触发器同步：每次新增/修改/删除比赛记录时，自动更新对应玩家的统计数据
2. 定时任务同步：每天凌晨执行统计SQL，将结果覆盖写入汇总表

这种方式下，查询排行榜只需直接读取汇总表并排序，能大幅提升查询性能。

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