如何改写MySQL查询以同时返回排名与积分列？

现有SQL可获取指定玩家在各赛事日期的排名，用于展示排名随赛事推进的变化。现在需要在结果中同时显示该玩家对应日期的积分总和，原查询通过标量子查询仅能返回排名，尝试使用JOIN时遇到困难，需要改写查询得到包含Date、Rank、Points的结果。

现有SQL语句 #

SELECT `Date`,
    -- Select rank based on the date given
    (SELECT `ranking`.`Rank` FROM (SELECT 
    `c`.`PlayerId` AS `PlayerId`,
    SUM(`c0`.`Points`) AS `Points`,
    RANK() OVER (ORDER BY SUM(`c0`.`Points`) DESC) AS `Rank`
    FROM `CompetitionResultPlayer` AS `c` 
    INNER JOIN `CompetitionResults` AS `c0` ON `c`.`ResultId` = `c0`.`Id`
    INNER JOIN `Competitions` AS `c1` ON `c0`.`CompetitionId` = `c1`.`Id`
    INNER JOIN `Tournaments` AS `t` ON `c1`.`TournamentId` = `t`.`Id`
    WHERE (`c1`.`EventId` = 2) 
        AND `t`.`EndDate` BETWEEN CAST('2023-01-01' AS DateTime) AND CAST(`Date` AS DateTime) 
        AND EXISTS (
            SELECT 1
            FROM `RankingTournament` AS `r`
            WHERE (`t`.`Id` = `r`.`TournamentId`) AND (`r`.`RankingId` = 1))
    GROUP BY `c`.`PlayerId`) AS `ranking` WHERE `ranking`.`PlayerId` = 97751) AS `Rank`
    FROM 
    -- Get the dates that has changes for the ranking and event
    (SELECT DISTINCT EndDate AS `Date` 
    FROM `Competitions` AS `date_c`
    INNER JOIN `Tournaments` AS `date_t` ON `date_c`.`TournamentId` = `date_t`.`Id`
        WHERE (`date_c`.`EventId` = 2) 
        AND `date_t`.`EndDate` BETWEEN CAST('2023-01-01' AS DateTime) AND CAST('2023-12-2' AS DateTime) 
        AND EXISTS (
            SELECT 1
            FROM `RankingTournament` AS `date_r`
            WHERE (`date_t`.`Id` = `date_r`.`TournamentId`) AND (`date_r`.`RankingId` = 1))
            ) AS `Date`

当前查询结果 #

预期结果 #

相关表示例数据 #

Rankings（用户可选择不同类型排名的表） #

RankingTournament（关联排名与赛事的中间表，排名仅展示该表内的赛事） #

Tournaments（EndDate列为排名变化的依据） #

Competitions（关联赛事与项目的表，如羽毛球男单、男双） #

CompetitionResults（存储赛事结果，包含用于计算排名的积分） #

CompetitionResultPlayer（关联玩家与赛事结果的表，适用于多人项目） #

改写后的SQL查询 #

SELECT 
    `dates`.`Date`,
    `player_ranking`.`Rank`,
    `player_ranking`.`Points`
FROM 
    -- 获取所有需要统计的赛事日期
    (SELECT DISTINCT EndDate AS `Date` 
     FROM `Competitions` AS `date_c`
     INNER JOIN `Tournaments` AS `date_t` ON `date_c`.`TournamentId` = `date_t`.`Id`
     WHERE (`date_c`.`EventId` = 2) 
       AND `date_t`.`EndDate` BETWEEN CAST('2023-01-01' AS DateTime) AND CAST('2023-12-2' AS DateTime) 
       AND EXISTS (
           SELECT 1
           FROM `RankingTournament` AS `date_r`
           WHERE (`date_t`.`Id` = `date_r`.`TournamentId`) AND (`date_r`.`RankingId` = 1))
    ) AS `dates`
INNER JOIN 
    -- 针对每个日期计算所有玩家的排名和积分
    (SELECT 
        `dates_sub`.`Date`,
        `c`.`PlayerId`,
        SUM(`c0`.`Points`) AS `Points`,
        RANK() OVER (PARTITION BY `dates_sub`.`Date` ORDER BY SUM(`c0`.`Points`) DESC) AS `Rank`
     FROM `CompetitionResultPlayer` AS `c` 
     INNER JOIN `CompetitionResults` AS `c0` ON `c`.`ResultId` = `c0`.`Id`
     INNER JOIN `Competitions` AS `c1` ON `c0`.`CompetitionId` = `c1`.`Id`
     INNER JOIN `Tournaments` AS `t` ON `c1`.`TournamentId` = `t`.`Id`
     -- 关联日期表，确保每个日期都计算一次排名
     INNER JOIN (SELECT DISTINCT EndDate AS `Date` 
                 FROM `Competitions` AS `date_c`
                 INNER JOIN `Tournaments` AS `date_t` ON `date_c`.`TournamentId` = `date_t`.`Id`
                 WHERE (`date_c`.`EventId` = 2) 
                   AND `date_t`.`EndDate` BETWEEN CAST('2023-01-01' AS DateTime) AND CAST('2023-12-2' AS DateTime) 
                   AND EXISTS (
                       SELECT 1
                       FROM `RankingTournament` AS `date_r`
                       WHERE (`date_t`.`Id` = `date_r`.`TournamentId`) AND (`date_r`.`RankingId` = 1))
                ) AS `dates_sub` ON `t`.`EndDate` <= `dates_sub`.`Date`
     WHERE (`c1`.`EventId` = 2) 
       AND EXISTS (
           SELECT 1
           FROM `RankingTournament` AS `r`
           WHERE (`t`.`Id` = `r`.`TournamentId`) AND (`r`.`RankingId` = 1))
     GROUP BY `dates_sub`.`Date`, `c`.`PlayerId`
    ) AS `player_ranking` ON `dates`.`Date` = `player_ranking`.`Date` 
                         AND `player_ranking`.`PlayerId` = 97751
ORDER BY `dates`.`Date`;

改写思路 #

1. 把原查询中计算排名的标量子查询改为独立子查询player_ranking，通过PARTITION BY dates_sub.Date确保每个日期的排名独立计算，同时保留积分字段。
2. 将日期表dates与player_ranking做JOIN，关联条件为日期匹配+目标玩家ID，一次性获取日期、排名、积分三个字段。
3. 解决了原标量子查询只能返回单列的限制，通过JOIN实现多字段同步输出。

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