如何筛选连续出勤天数最多的员工？SQL实现方案咨询

你的原代码问题在于：同一CTE中定义的counter列无法被同层的lag()函数引用，而且你也没写递归CTE的递归分支，导致逻辑不生效。下面提供两种可行的counter定义方式，来实现需求：

方法一：利用日期与行号的差值分组 #

核心思路是：连续出勤的日期，减去按日期排序的行号后，结果会保持一致，以此作为连续段的标识。

WITH ranked AS (
    SELECT 
        id,
        date,
        present,
        -- 按员工分组，日期排序生成行号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
    FROM Presence
),
continuous_groups AS (
    SELECT 
        id,
        present,
        -- 连续出勤的日期减去行号会得到相同值，作为分组键
        DATE_SUB(date, INTERVAL rn DAY) AS group_key
    FROM ranked
    WHERE present = 1 -- 只考虑出勤记录
),
max_continuous AS (
    SELECT 
        id,
        COUNT(*) AS continuous_days,
        -- 标记全局最大连续出勤天数
        MAX(COUNT(*)) OVER () AS global_max_days
    FROM continuous_groups
    GROUP BY id, group_key
)
SELECT DISTINCT id
FROM max_continuous
WHERE continuous_days = global_max_days;

方法二：用窗口函数直接计算连续天数 #

通过LAG()判断前一天是否出勤，生成连续段的起始标识，再累加计算连续天数：

WITH attendance_flags AS (
    SELECT 
        id,
        date,
        present,
        -- 当当天出勤且前一天未出勤时，标记为新连续段的开始
        CASE 
            WHEN present = 1 AND LAG(present, 1, 0) OVER (PARTITION BY id ORDER BY date) = 0 
            THEN 1 
            ELSE 0 
        END AS new_segment
    FROM Presence
),
segment_groups AS (
    SELECT 
        id,
        date,
        present,
        -- 累加新段标识，得到每个连续段的分组ID
        SUM(new_segment) OVER (PARTITION BY id ORDER BY date) AS segment_id
    FROM attendance_flags
    WHERE present = 1
),
continuous_stats AS (
    SELECT 
        id,
        COUNT(*) AS continuous_days,
        MAX(COUNT(*)) OVER () AS global_max
    FROM segment_groups
    GROUP BY id, segment_id
)
SELECT DISTINCT id
FROM continuous_stats
WHERE continuous_days = global_max;

说明 #

两种方法都不需要递归CTE，适配你的场景：

• 方法一利用日期无间隔的特性，计算更简洁
• 方法二通用型更强，即使日期有间隔也能通过调整逻辑适配

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