使用LISTAGG按教师、课程、学期分组聚合学生信息的SQL实现

直接基于你已写的左连逻辑补充选课、学生表关联，加分组聚合即可，完整语句：

SELECT
    t.teacher_id,
    t.first_name AS teacher_first_name,
    t.last_name AS teacher_last_name,
    c.course_id,
    c.course_name,
    c.semester,
    COUNT(e.student_id) AS student_count,
    LISTAGG(s.first_name || ' ' || s.last_name, ', ') 
        WITHIN GROUP (ORDER BY s.student_id) AS student_name_list
FROM teachers t
LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
LEFT JOIN enrollment e
    ON c.course_id = e.course_id
LEFT JOIN students s
    ON e.student_id = s.student_id
GROUP BY
    t.teacher_id,
    t.first_name,
    t.last_name,
    c.course_id,
    c.course_name,
    c.semester
ORDER BY t.teacher_id;

关键说明 #

• 全程使用左连接，和你初始查询的返回范围一致：没有对应课程的教师、没有学生选课的课程都不会被过滤
• 学生人数统计用COUNT(e.student_id)而非COUNT(*)：无选课时选课表关联字段为NULL，COUNT会自动忽略NULL，这类课程的人数会正确返回0，不会误统计为1
• LISTAGG先拼接学生名和姓为完整姓名，以, 为分隔符按学生ID排序聚合；如果后续单组学生过多导致字符串超长，可追加ON OVERFLOW TRUNCATE '...'子句避免报错

测试数据匹配结果 #

用你提供的测试数据执行后，结果符合预期：

• 101号教师的Geometry课共5人，学生名单为Faith Aaron, Lisa Saladino, Leslee Altman, Patty Kern, Beth Cooper
• 102号教师的Trigonometry课共5人，学生名单同上
• 103号教师的Calculus课共6人，在上述5人基础上增加Jill Coralnick
• 104、105、106号教师的Chemistry、Biology、Physcology课暂无选课记录，学生数为0，学生名单为空

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