这是我的示例数据集。在 MySQL 中寻找代码
期望的输出
学生 | 次 |
---|---|
美国广播公司 | 3 (学生abc没有连续报考2021年T3、T4和2022年T1的任何科目) |
xyz | 4 (学生xyz未连续报读2021年T3、T4、2022年T1、T2任何科目) |
代码:
create table test_student (student varchar(25), Term char (2),
year int, Subject varchar(20));
insert into test_student
values ("abc","t1","2021","sub1"),
("abc","t2","2021","sub2"),
("abc","t3","2021",""),
("abc","t4","2021",""),
("abc","t1","2022",""),
("abc","t2","2022","sub3"),
("abc","t3","2022",""),
("abc","t4","2022",""),
("abc","t1","2023","sub4"),
("abc","t2","2023",""),
("abc","t3","2023","sub5"),
("XYZ","t1","2021","sub1"),
("XYZ","t2","2021","sub2"),
("XYZ","t3","2021",""),
("XYZ","t4","2021",""),
("XYZ","t1","2022",""),
("XYZ","t2","2022",""),
("XYZ","t3","2022","sub3"),
("XYZ","t4","2022",""),
("XYZ","T1","2023",""),
("XYZ","t2","2023","sub4"),
("XYZ","t3","2023","sub5");
下面的代码给我输出 abc: 3 xyz: 3
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY student, subject ORDER BY term) AS term_num
FROM test_student
)
SELECT student, MAX(gap_length) AS max_gap
FROM (
SELECT student,
term_num - ROW_NUMBER() OVER (PARTITION BY student, year ORDER BY term) AS gap_length
FROM cte
WHERE subject = ''
) AS gaps
GROUP BY student;