MySQL多表关联查询：输出学生及四个志愿的专业信息

涉及表结构回顾 #

• student_tbl：学生主表，主键ID，Name字段存学生姓名
• majorsubjects：专业主表，主键id，subName字段存专业名称
• entries_tbl：志愿填报记录表，外键student_ID关联学生表、subject_id关联专业表，preference字段存1-4的志愿优先级，每个学生固定对应4条不同专业的志愿记录

实现思路 #

核心是做行转列聚合：原志愿数据按优先级分多行存储，需要按学生维度分组，把不同优先级的专业提取为独立的横向列，相比多次自连接志愿表的写法，条件聚合的执行效率更高。

可直接执行的SQL语句 #

SELECT
  s.ID AS '学生id',
  s.Name AS 'name',
  MAX(CASE WHEN e.preference = 1 THEN m.subName END) AS pref1,
  MAX(CASE WHEN e.preference = 2 THEN m.subName END) AS pref2,
  MAX(CASE WHEN e.preference = 3 THEN m.subName END) AS pref3,
  MAX(CASE WHEN e.preference = 4 THEN m.subName END) AS pref4
FROM student_tbl s
LEFT JOIN entries_tbl e
  ON s.ID = e.student_ID
LEFT JOIN majorsubjects m
  ON e.subject_id = m.id
GROUP BY s.ID, s.Name;

注意事项 #

• 这里用LEFT JOIN是为了兼容个别学生漏填志愿的场景，不会丢失学生基础数据，如果业务上确认所有学生都完整填了4个志愿，替换成INNER JOIN性能会略好
• 聚合函数选MAX()是因为按学生分组后，每个志愿优先级只会匹配到1个有效专业名，其余行的条件判断返回NULL，MAX()会自动忽略NULL值取到唯一的专业名称，换成MIN()效果完全一致
• 不要用4次自连接志愿表分别筛选1-4志愿的写法，当志愿表数据量较大时，查询性能会比条件聚合差很多

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