如何用T-SQL查询高尔夫数据库中单轮连续低于标准杆的最高记录？

可以用递归CTE实现这个需求，同时结合窗口函数的方案在数据量较大时性能更优。下面分别给出两种实现方式，重点说明递归CTE的思路：

前置数据关联与标记 #

首先关联Results和Holes表，标记每个球员每轮中低于标准杆的球洞，并按球洞编号排序（连续序列是按球洞顺序判定的）：

WITH PlayerRoundHoles AS (
    SELECT 
        r.PlayerId,
        r.RoundId,
        h.Number AS HoleNumber,
        -- 标记当前球洞是否低于标准杆
        CASE WHEN r.Score < h.Par THEN 1 ELSE 0 END AS IsUnderPar
    FROM Results r
    JOIN Holes h ON r.HoleId = h.HoleId
    ORDER BY r.PlayerId, r.RoundId, h.Number
)

方案一：递归CTE实现连续序列追踪 #

递归CTE通过逐洞递推，追踪连续低于标准杆的序列长度和对应球洞编号：

WITH PlayerRoundHoles AS (
    SELECT 
        r.PlayerId,
        r.RoundId,
        h.Number AS HoleNumber,
        CASE WHEN r.Score < h.Par THEN 1 ELSE 0 END AS IsUnderPar
    FROM Results r
    JOIN Holes h ON r.HoleId = h.HoleId
),
RecursiveUnderPar AS (
    -- 锚点成员：初始化第1洞的连续序列状态
    SELECT 
        PlayerId,
        RoundId,
        HoleNumber,
        IsUnderPar,
        CASE WHEN IsUnderPar = 1 THEN 1 ELSE 0 END AS ConsecutiveCount,
        CASE WHEN IsUnderPar = 1 THEN CAST(HoleNumber AS VARCHAR(MAX)) ELSE '' END AS HoleList
    FROM PlayerRoundHoles
    WHERE HoleNumber = 1
    UNION ALL
    -- 递归成员：逐洞更新连续序列状态
    SELECT 
        prh.PlayerId,
        prh.RoundId,
        prh.HoleNumber,
        prh.IsUnderPar,
        -- 当前洞低于标准杆则延续序列，否则重置计数
        CASE WHEN prh.IsUnderPar = 1 THEN rup.ConsecutiveCount + 1 ELSE 0 END AS ConsecutiveCount,
        -- 当前洞低于标准杆则追加球洞编号，否则重置列表
        CASE WHEN prh.IsUnderPar = 1 THEN rup.HoleList + ',' + CAST(prh.HoleNumber AS VARCHAR(MAX)) ELSE '' END AS HoleList
    FROM PlayerRoundHoles prh
    JOIN RecursiveUnderPar rup 
        ON prh.PlayerId = rup.PlayerId 
        AND prh.RoundId = rup.RoundId 
        AND prh.HoleNumber = rup.HoleNumber + 1
),
-- 筛选有效序列并排序，取每个球员每轮的最长序列
MaxConsecutive AS (
    SELECT 
        PlayerId,
        RoundId,
        ConsecutiveCount AS NumberOfConsecutiveUnderParScores,
        HoleList AS UnderParHoleNumbers,
        ROW_NUMBER() OVER (PARTITION BY PlayerId, RoundId ORDER BY ConsecutiveCount DESC) AS SeqNum
    FROM RecursiveUnderPar
    WHERE ConsecutiveCount > 0
)
SELECT 
    PlayerId,
    RoundId,
    NumberOfConsecutiveUnderParScores,
    UnderParHoleNumbers
FROM MaxConsecutive
WHERE SeqNum = 1
ORDER BY NumberOfConsecutiveUnderParScores DESC;

方案二：窗口函数实现（更高效） #

对于大数据量场景，用窗口函数标记连续序列分组的方式性能更好：

WITH PlayerRoundHoles AS (
    SELECT 
        r.PlayerId,
        r.RoundId,
        h.Number AS HoleNumber,
        CASE WHEN r.Score < h.Par THEN 1 ELSE 0 END AS IsUnderPar
    FROM Results r
    JOIN Holes h ON r.HoleId = h.HoleId
    ORDER BY r.PlayerId, r.RoundId, h.Number
),
GroupedUnderPar AS (
    SELECT 
        PlayerId,
        RoundId,
        HoleNumber,
        IsUnderPar,
        -- 标记连续序列的分组ID：序列中断时分组ID递增
        SUM(CASE WHEN IsUnderPar = 1 AND LAG(IsUnderPar, 1, 0) OVER (PARTITION BY PlayerId, RoundId ORDER BY HoleNumber) = 1 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY PlayerId, RoundId ORDER BY HoleNumber) AS GroupId
    FROM PlayerRoundHoles
),
ConsecutiveStats AS (
    SELECT 
        PlayerId,
        RoundId,
        COUNT(*) AS NumberOfConsecutiveUnderParScores,
        STRING_AGG(HoleNumber, ',') WITHIN GROUP (ORDER BY HoleNumber) AS UnderParHoleNumbers,
        ROW_NUMBER() OVER (PARTITION BY PlayerId, RoundId ORDER BY COUNT(*) DESC) AS SeqNum
    FROM GroupedUnderPar
    WHERE IsUnderPar = 1
    GROUP BY PlayerId, RoundId, GroupId
)
SELECT 
    PlayerId,
    RoundId,
    NumberOfConsecutiveUnderParScores,
    UnderParHoleNumbers
FROM ConsecutiveStats
WHERE SeqNum = 1
ORDER BY NumberOfConsecutiveUnderParScores DESC;

关键说明 #

• 递归CTE的核心是从第1洞开始逐洞递推，实时更新连续序列的长度和球洞列表，序列中断时重置状态。
• 窗口函数方案通过LAG函数识别连续序列的中断点，用分组ID聚合统计每个连续序列的信息，执行效率更高。
• 最终结果会返回每个球员每轮中最长的连续低于标准杆序列，若存在多个长度相同的序列，默认返回最早开始的那个。

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