SQL Server 2016中查询学生连续3天及以上缺勤的行组

嗨，这个问题属于经典的连续序列检测场景，在SQL Server 2016中，我们可以借助窗口函数来高效实现需求，具体思路和代码如下：

核心思路 #

我们需要给每个学生的缺勤记录按日期排序，然后通过计算一个"分组键"来把连续日期的记录归为同一组——连续的日期减去对应的行号后，会得到相同的日期值，以此作为分组依据。之后统计每个分组的记录数，筛选出记录数≥3的分组即可。

完整SQL代码 #

WITH AttendanceWithRowNum AS (
    -- 第一步：给每个学生的缺勤记录按日期排序，生成行号
    SELECT 
        ID,
        StudentID,
        Date,
        AbsenceReasonID,
        ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY Date) AS RowNum
    FROM Attendance
),
GroupedAttendance AS (
    -- 第二步：计算分组键，并统计每个分组的记录数量
    SELECT 
        *,
        DATEADD(DAY, -RowNum, Date) AS GroupKey,
        COUNT(*) OVER (PARTITION BY StudentID, DATEADD(DAY, -RowNum, Date)) AS GroupSize
    FROM AttendanceWithRowNum
)
-- 第三步：筛选出连续缺勤≥3次的记录
SELECT 
    ID,
    StudentID,
    Date,
    AbsenceReasonID
FROM GroupedAttendance
WHERE GroupSize >= 3
ORDER BY StudentID, Date;

代码解释 #

1. AttendanceWithRowNum CTE：使用ROW_NUMBER()窗口函数，按StudentID分组、Date升序排序，给每个学生的每条缺勤记录分配一个递增的行号。
2. GroupedAttendance CTE：通过DATEADD(DAY, -RowNum, Date)计算分组键——连续的日期减去对应的行号后，结果会一致（比如2018-02-02减1、2018-02-03减2、2018-02-04减3，结果都是2018-02-01）。再用COUNT()窗口函数统计每个分组的记录数GroupSize。
3. 最终查询：筛选出GroupSize ≥3的记录，就是连续缺勤3次及以上的行组。

测试结果 #

针对你提供的样本数据，执行上述代码后，会返回StudentID=10158的3条连续缺勤记录，其他学生的记录因连续次数不足3次会被排除。

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