这是我的示例数据集。在 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;
有多种寻找岛屿的技术。其中一个确实涉及两个
ROW_NUMBER
调用,尽管与您一直在做的方式不完全相同。小提琴
这是一种无需窗口函数即可获得所需结果的方法:
这假设每个学生/年/学期只有一个记录。如果需要,您可以通过一些额外的子查询/分组来解决这个问题。
思维过程是:
我不得不将 year+term 转换为“计数器”,以便进行一些数学运算以找出将表格连接到自身时的间隙大小。这可以通过计数/查找表来简化。这是计数器的示例:
我想你使用的是 MySQL 8.x。exists 函数使它成为 MySQL 5.7 以上版本的理想选择