MariaDB多表关联PIVOT行转列查询学生对应科目最新授课日期

核心逻辑说明 #

我们可以分两步实现需求：首先聚合得到每个科目对应每个学生的最新有效授课日期，再通过静态行转列得到最终的科目标-学生交叉表。

步骤1：中间结果聚合查询 #

先关联4张表，过滤出同时满足「学生当日出勤、科目在当日排课中、学生选了该节课」的记录，按科目和学生分组取最大日期：

SELECT 
  t.teach AS subject,
  s.student,
  MAX(tt.date) AS LDUS
FROM table_toteach t
-- 关联课程表，匹配科目出现在当日3节排课任意一节
JOIN table_timetable tt 
  ON t.teach IN (tt.subj1, tt.subj2, tt.subj3)
-- 关联学生表，匹配该学生在本节课的选课名单中
JOIN table_students s
  ON FIND_IN_SET(s.student, tt.stnames) > 0
-- 关联出勤表，确认学生当日早间出勤
JOIN table_stpresents sp
  ON sp.student = s.student 
  AND sp.Date = tt.date 
  AND sp.present = 1
-- 此处可添加科目组过滤条件，例如 WHERE t.`teach-id` IN (xx,xx)
GROUP BY t.teach, s.student

步骤2：静态行转列实现 #

你可以根据当前查询的学生列表，动态拼接每个学生的列判断逻辑，示例如下（假设当前学生为张三、李四、王五）：

SELECT 
  subject,
  MAX(IF(student = '张三', LDUS, NULL)) AS `张三`,
  MAX(IF(student = '李四', LDUS, NULL)) AS `李四`,
  MAX(IF(student = '王五', LDUS, NULL)) AS `王五`
FROM (
  -- 此处插入步骤1的中间聚合查询
  SELECT 
    t.teach AS subject,
    s.student,
    MAX(tt.date) AS LDUS
  FROM table_toteach t
  JOIN table_timetable tt 
    ON t.teach IN (tt.subj1, tt.subj2, tt.subj3)
  JOIN table_students s
    ON FIND_IN_SET(s.student, tt.stnames) > 0
  JOIN table_stpresents sp
    ON sp.student = s.student 
    AND sp.Date = tt.date 
    AND sp.present = 1
  GROUP BY t.teach, s.student
) mid_result
GROUP BY subject

注意事项 #

• 若学生名称本身包含逗号，会导致FIND_IN_SET匹配异常，需提前保证学生名称无逗号。
• 最终结果中NULL值代表该学生没有符合条件的授课记录，可在UI层自行替换为空值或自定义占位符。
• 切换科目组时只需修改中间查询的过滤条件，学生列可根据当前范围内的学生列表动态拼接IF语句即可。

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