连续缺勤的学生（不包括假期）-解网

问：

我想找回 124 名 ID 学生，因为我希望在过去 8 天内连续缺席 4 天的学生，忽略周末 H.124 学生在周末前连续缺席了 3 天，周一周末之后他也缺席了，所以他是要找回的人，因为他连续缺席了 4 天

我试过这个工作正常，但这个查询仅适用于 MySQL 8.1，而我使用的是旧版本 5.6.41-84.1 和 PHP 版本：7.4.33。

如何在我的MySQL版本上执行此操作？

attendance_id	时间戳	student_id	地位
1	2023-11-05	124	P
2	2023-11-05	125	P
3	2023-11-06	124	一个
4	2023-11-06	125	P
5	2023-11-07	124	一个
6	2023-11-07	125	P
7	2023-11-08	124	一个
8	2023-11-08	125	P
9	2023-11-09	124	H
10	2023-11-09	125	H
11	2023-11-10	124	H
12	2023-11-10	125	H
13	2023-11-11	124	一个
14	2023-11-11	125	P
15	2023-11-12	124	P
16	2023-11-12	125	P

$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 间隙和孤岛

select student_id, timestamp,
       case when status = 'A' and student_id = @student then @absent := @absent + 1
            when status = 'A' and student_id <> @student then @absent := 1
            when status = 'P' then @absent := 0 end as absent,
       case when student_id <> @student then @student := student_id end as tracking_student
from attendance, (select @absent := 0, @student := -1) as init
where status in ('A', 'P')
order by student_id, timestamp

看到这个 db-fiddle

student_id	时间戳	缺席	tracking_student
124	2023-11-05	0	124
124	2023-11-06	1	零
124	2023-11-07	2	零
124	2023-11-08	3	零
124	2023-11-11	4	零
124	2023-11-12	0	零
125	2023-11-05	1	125
125	2023-11-06	0	零
125	2023-11-07	0	零
125	2023-11-08	0	零
125	2023-11-11	0	零
125	2023-11-12	0	零

2赞 user1191247 11/12/2023 #2

有人建议你应该升级四年前最好的部分。你真的应该停止拖延。

这是一个可怕的解决方案，但我认为它有效。它会创建一个类似于基于 ROW_NUMBER 的查询中的分组：

SELECT
    student_id,
    MIN(timestamp) AS ts_start,
    MAX(timestamp) AS ts_end
FROM (
    SELECT attendance.*,
        IF (@prev_student <> student_id, (@prev_student := student_id) AND (@grp := 0), null) AS reset,
        @grp := IF (status = 'P', @grp + 1, @grp) AS grp
    FROM attendance
    JOIN (SELECT @prev_student := 0, @grp := 0) AS init
    WHERE status IN ('A', 'P')
    AND timestamp BETWEEN CURRENT_DATE - INTERVAL 8 DAY
                      AND CURRENT_DATE
    ORDER BY student_id, timestamp
) a
WHERE status = 'A'
GROUP BY student_id, grp
HAVING COUNT(*) >= 4;

派生的表是：

attendance_id	时间戳	student_id	地位	重置	玻璃钢
1	2023-11-05	124	P	0	1
3	2023-11-06	124	一个	零	1
5	2023-11-07	124	一个	零	1
7	2023-11-08	124	一个	零	1
13	2023-11-11	124	一个	零	1
15	2023-11-12	124	P	零	2
2	2023-11-05	125	一个	0	0
4	2023-11-06	125	P	零	1
6	2023-11-07	125	P	零	2
8	2023-11-08	125	P	零	3
14	2023-11-11	125	P	零	4
16	2023-11-12	125	P	零	5

完整的查询输出：

student_id	ts_start	ts_end
124	2023-11-06	2023-11-11

这是一个 db<>小提琴。

连续缺勤的学生（不包括假期）

Consecutive Absent Students Excluding Holidays

评论

评论