如何修改T-SQL查询筛选得分高于上一条的赛季最高得分记录

你当前的T-SQL查询已经能正确提取每个赛季的最高得分球员列表，现在需要进一步过滤，只保留得分高于上一条选中记录的条目——也就是找出那些赛季得分打破之前纪录的记录。

原查询 #

WITH CTE AS ( 
    SELECT 
        m.Season AS 'Season', 
        SUM(bi.Runs) AS 'Runs', 
        p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) AS 'PlayerName' 
    FROM Player p 
    JOIN BatInnings bi on bi.fk_Player_Id = p.id 
    JOIN Innings i on i.Id = bi.fk_Innings_Id 
    JOIN Team t on t.id = i.fk_Team_Id 
    JOIN Match m on m.id = i.fk_Match_Id 
    WHERE 
        (p.id = @playerId OR @playerId IS NULL) 
        AND m.MatchType IN (@matchType1, @matchType2, @matchType3) 
        AND (i.fk_Team_Id = @teamId OR @teamId IS NULL) 
        AND (t.fk_Club_Id = @clubId OR @clubId IS NULL) 
    GROUP BY m.season, p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) 
) 
SELECT CTE.* 
FROM CTE 
WHERE CTE.Runs = (SELECT MAX(CTE2.Runs) FROM CTE CTE2 WHERE CTE2.Season = CTE.Season) 
ORDER BY CTE.Season

原查询结果 #

期望结果 #

修改后的查询 #

我们可以在原CTE的基础上，新增一个步骤来计算上一条记录的得分，然后进行筛选：

WITH CTE AS ( 
    SELECT 
        m.Season AS 'Season', 
        SUM(bi.Runs) AS 'Runs', 
        p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) AS 'PlayerName' 
    FROM Player p 
    JOIN BatInnings bi on bi.fk_Player_Id = p.id 
    JOIN Innings i on i.Id = bi.fk_Innings_Id 
    JOIN Team t on t.id = i.fk_Team_Id 
    JOIN Match m on m.id = i.fk_Match_Id 
    WHERE 
        (p.id = @playerId OR @playerId IS NULL) 
        AND m.MatchType IN (@matchType1, @matchType2, @matchType3) 
        AND (i.fk_Team_Id = @teamId OR @teamId IS NULL) 
        AND (t.fk_Club_Id = @clubId OR @clubId IS NULL) 
    GROUP BY m.season, p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) 
),
SeasonHighs AS (
    SELECT 
        CTE.*,
        -- 获取上一条记录的得分，按赛季排序
        LAG(CTE.Runs) OVER(ORDER BY CTE.Season) AS PreviousHighRuns
    FROM CTE 
    WHERE CTE.Runs = (SELECT MAX(CTE2.Runs) FROM CTE CTE2 WHERE CTE2.Season = CTE.Season) 
)
SELECT 
    Season,
    Runs,
    PlayerName AS Player
FROM SeasonHighs
-- 筛选条件：第一条记录（PreviousHighRuns为NULL） 或者 当前得分高于上一条的最高得分
WHERE PreviousHighRuns IS NULL OR Runs > PreviousHighRuns
ORDER BY Season;

思路说明 #

1. 保留原CTE逻辑：继续用原CTE计算每个球员在各赛季的总得分。
2. 新增SeasonHighs CTE：这里先获取每个赛季的最高得分记录，同时使用LAG()窗口函数，按赛季排序后，获取上一个赛季最高得分记录的分值。
3. 最终筛选：只保留两种记录：
   • 第一条记录（也就是最早的赛季，没有上一条记录，PreviousHighRuns为NULL）
   • 当前赛季的最高得分大于上一个赛季最高得分的记录

这样就能得到你期望的“破纪录”赛季得分列表了。

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