基于SQL的学生出勤按日分段排名问题求助

需求说明 #

按日对学生出勤情况进行排名，规则如下：

• 每个学生单独计算排名
• 缺勤（N）当日排名为0
• 出勤（Y）的连续时段从1开始递增排名，被缺勤打断后，新的连续出勤时段需重新从1开始计数

原始数据 #

预期输出 #

当前错误输出 #

问题分析 #

原代码的核心问题在于：

RANK() OVER (PARTITION BY studentid,attendance ORDER BY [Date])

该写法将同一个学生的所有出勤（Y）记录归为同一分区，导致排名会跨连续时段累加，无法在缺勤打断后重置为1。

修正后的SQL代码 #

WITH DailyAttendance AS (
    -- 将原始区间数据拆分为单日记录
    SELECT 
        studentid,
        DATEADD(day, n - 1, startdate) AS [Date],
        attendance
    FROM 
        YourOriginalTable
    CROSS APPLY (
        -- 生成区间内的日期序列
        SELECT TOP (DATEDIFF(day, startdate, enddate) + 1) 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM master..spt_values
    ) AS Numbers
),
AttendanceGroups AS (
    SELECT 
        studentid,
        [Date],
        attendance,
        -- 标记连续出勤的分组ID：每当出勤从N转为Y时，创建新分组
        SUM(CASE WHEN attendance = 'Y' AND LAG(attendance, 1, 'N') OVER (PARTITION BY studentid ORDER BY [Date]) = 'N' THEN 1 ELSE 0 END) 
        OVER (PARTITION BY studentid ORDER BY [Date]) AS group_id
    FROM DailyAttendance
)
SELECT 
    studentid,
    [Date],
    attendance,
    CASE 
        WHEN attendance = 'N' THEN 0
        -- 每个连续出勤分组内从1开始递增排名
        ELSE ROW_NUMBER() OVER (PARTITION BY studentid, group_id ORDER BY [Date])
    END AS 排名
FROM AttendanceGroups
ORDER BY studentid, [Date];

代码说明 #

1. DailyAttendance CTE：将原始的日期区间数据拆分为单日记录，确保每一天都有独立的出勤记录。
2. AttendanceGroups CTE：通过LAG()函数获取前一天的出勤状态，当当前为Y且前一天为N时，标记新分组；通过累加标记值得到每个连续出勤时段的唯一分组ID。
3. 最终查询：缺勤记录直接返回0，出勤记录则在每个学生的分组内用ROW_NUMBER()从1开始计数，实现连续时段的排名重置。

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