多组离校返校日期的查询与排序实现方案咨询

核心思路 #

用ROW_NUMBER()给每个学生的动作按日期排序，通过序号分组配对离校和返校日期，生成对应缺勤次数的结构化数据。因为每个学生的离校-返校是严格按时间顺序推进的，ROW_NUMBER()能确保每个动作有唯一的顺序标识，避免RANK可能带来的序号重复问题，更适配你的业务场景。

具体实现步骤 #

1. 给动作添加顺序编号：按学生ID分组，按生效日期排序，给每个动作分配唯一序号。
2. 推导缺勤次数：通过序号将离校（奇数序号）和返校（偶数序号）归为同一缺勤次数组。
3. 聚合日期数据：将同一缺勤次数的离校、返校日期合并到一行，形成结构化表格。

SQL示例 #

假设你的Action字段值为'离校'和'返校'，可以用以下SQL实现：

WITH student_actions AS (
    SELECT
        StudentID,
        Action,
        EffectiveDate,
        -- 按学生分组，给动作按日期排序生成唯一序号
        ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY EffectiveDate) AS action_seq
    FROM your_table_name
),
action_pairs AS (
    SELECT
        StudentID,
        -- 计算缺勤次数：将离校(1/3/5)和返校(2/4/6)归为同一组
        (action_seq + 1) / 2 AS absence_number,
        CASE WHEN Action = '离校' THEN EffectiveDate END AS leave_date,
        CASE WHEN Action = '返校' THEN EffectiveDate END AS return_date
    FROM student_actions
)
SELECT
    StudentID,
    absence_number AS 缺勤次数,
    MAX(leave_date) AS 离校日期,
    MAX(return_date) AS 返校日期
FROM action_pairs
GROUP BY StudentID, absence_number
-- 可选：过滤无返校记录的未完成缺勤
-- HAVING MAX(leave_date) IS NOT NULL
ORDER BY StudentID, absence_number;

关键说明 #

• 选择ROW_NUMBER的原因：如果两个动作的EffectiveDate相同，RANK()会生成重复序号，导致缺勤次数配对混乱；而ROW_NUMBER()始终生成唯一顺序号，确保离校和返校能准确对应，匹配你最多3次缺勤的业务限制。
• 处理未完成缺勤：如果存在学生仅离校未返校的情况，可保留注释中的HAVING子句过滤，或用COALESCE给返校日期赋值为NULL。
• 适配3次缺勤限制：序号按1/2、3/4、5/6的逻辑自动生成1、2、3三个缺勤次数，完全覆盖你的需求。

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