如何用SQL生成含学生ID表头与课程明细结构的Feed文件

需求说明 #

需要将学生ID作为表头行（type=0），对应的课程信息作为明细行（type=1），生成符合指定格式的Feed数据。

原SQL语句 #

Select st.student_id, st.academics_year, st.academic_term, cl.class, cl.schedule, cl.days
from student_term st
left join classes cl on cl.year = st.academic_year and cl.term=st.academics_term

当前查询结果 #

12345 2022 spring mat01, 08:00 to 09:00 TW
12345 2022 spring fin101, 10:00 to 11:00 MF
12345 2022 spring bio101, 14:00 to 15:00 MF

期望输出格式 #

12345
mat01, 08:00 to 09:00 TW
fin101, 10:00 to 11:00 MF
bio101, 14:00 to 15:00 MF

-- 结构化输出格式
seq type  linetex
1    0    12345
1    0    45678
1    1    mat131  08:00 to 09:00
1    1    lit131  08:00 to 09:00
1    1    bio131  09:00 to 10:00

其中type=0对应表头行，type=1对应明细行。

解决方案 #

使用UNION ALL拆分表头与明细的查询逻辑，分别标记类型后合并结果：

通用SQL实现 #

-- 生成表头行（type=0）
SELECT DISTINCT
    1 AS seq,
    0 AS type,
    st.student_id AS linetex
FROM student_term st

UNION ALL

-- 生成明细行（type=1）
SELECT
    1 AS seq,
    1 AS type,
    CONCAT(cl.class, ', ', cl.schedule, ' ', cl.days) AS linetex
FROM student_term st
LEFT JOIN classes cl 
    ON cl.year = st.academic_year 
    AND cl.term = st.academics_term
WHERE cl.class IS NOT NULL  -- 过滤无课程的学生记录
ORDER BY type, st.student_id

关键说明 #

1. 第一部分通过DISTINCT提取唯一学生ID作为表头行，标记类型为0
2. 第二部分拼接课程字段生成明细行，标记类型为1
3. 排序规则保证同一学生的表头行在前，明细行紧随其后
4. 若需保留无课程的学生，删除WHERE cl.class IS NOT NULL即可

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