排除节假日的连续缺课学生查询：MySQL 5.6兼容实现方案

需要检索**最近8天内连续4天缺课（状态为A，忽略周末/节假日状态H）**的学生。例如学号124的学生：周末前连续3天缺课（A），周末后周一仍缺课（A），累计满足连续4天缺课的条件。

原查询语句依赖MySQL 8.1的窗口函数，但当前环境为MySQL 5.6.41-84.1 + PHP 7.4.33，需要适配低版本MySQL的实现方案。

数据表结构 #

原查询代码（仅MySQL 8.1+可用） #

$query = $this->db->query("
select *,
    student_id,
    min(timestamp) timestamp_start,
    max(timestamp) timestamp_end
from (
    select 
        t.*, 
        row_number() over(partition by student_id order by timestamp) rn1,
        row_number() over(partition by student_id, status order by timestamp) rn2
    from attendance t
) t
where status = 'A' AND timestamp BETWEEN (CURRENT_DATE() - INTERVAL 8 DAY) AND CURRENT_DATE()
group by student_id, rn1 - rn2
having count(*) >= 4");

MySQL 5.6不支持窗口函数，我们可以通过用户变量模拟row_number()的分组排序逻辑，核心思路是用变量生成全局序号和按学生+状态分组的序号，通过序号差值识别连续缺课的分组，最终统计符合条件的学生。

适配后的查询语句 #

$query = $this->db->query("
SELECT 
    student_id,
    MIN(timestamp) AS timestamp_start,
    MAX(timestamp) AS timestamp_end,
    COUNT(*) AS absent_days
FROM (
    SELECT 
        t.*,
        @rn1 := IF(@prev_student = student_id, @rn1 + 1, 1) AS rn1,
        @rn2 := IF(@prev_student = student_id AND @prev_status = status, @rn2 + 1, 1) AS rn2,
        @prev_student := student_id,
        @prev_status := status
    FROM (
        SELECT * 
        FROM attendance 
        WHERE timestamp BETWEEN (CURRENT_DATE() - INTERVAL 8 DAY) AND CURRENT_DATE()
        ORDER BY student_id, timestamp
    ) t
    CROSS JOIN (SELECT @prev_student := '', @prev_status := '', @rn1 := 0, @rn2 := 0) vars
) t
WHERE status = 'A'
GROUP BY student_id, rn1 - rn2
HAVING COUNT(*) >= 4");

逻辑说明 #

1. 最内层子查询先筛选最近8天的记录，并按学生ID、日期排序，确保数据顺序正确
2. 通过用户变量@rn1生成每个学生的全局递增序号，@rn2生成每个学生同一状态的递增序号
3. rn1 - rn2的差值相同，代表同一连续状态的分组（比如连续的A会有相同差值）
4. 最后分组统计每个学生的连续A分组的记录数，筛选出缺课天数≥4的结果

注意事项 #

• 确保timestamp字段为日期类型（或可正确排序的字符串），避免排序逻辑出错
• 变量必须通过CROSS JOIN初始化，防止MySQL变量初始化顺序问题
• 当前逻辑中H状态的记录不会进入最终统计，自动实现了忽略节假日/周末的需求

内容的提问来源于stack exchange，提问作者Deep Blue See