查询T-1、T、T+1学期间学生复学率的技术需求

需按**连续学期（T-1、T、T+1，T代表当前学期）**维度统计学生复学率。复学定义为：学生在T学期有选课记录（Subject字段非空），且T-1学期处于暂停学习状态（Subject字段为空）。

学生状态示例 #

• 学生ABC：
  • 2023年T2学期暂停学习
  • 2023年T3学期复学
  • 2023年T4学期暂停学习
  • 2024年T1学期复学
• 学生XYZ：无复学记录

测试数据SQL代码 #

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"),
("abc","t4","2023",""),
("abc","t1","2024","sub 6"),
("XYZ","t1","2021","subl"),
("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");

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