PostgreSQL中SELECT语句统计课程报名学生数的实现问题

报错原因 #

你遇到的GROUP BY报错是SQL的标准执行规则：当查询中使用COUNT/SUM这类聚合函数时，所有没有包裹在聚合函数内的查询字段，都需要显式添加到GROUP BY子句中，否则数据库无法确定非聚合字段的返回逻辑。

解决方案 #

方案1：先单独统计报名人数再关联（更稳妥，不易出错） #

先用CTE预统计每门课程的报名人数，再关联到你原有查询逻辑中，不需要修改原有查询的主体结构：

WITH course_student_count AS (
    -- 预统计每门课的报名人数
    SELECT 
        course_id,
        COUNT(id_record) AS students_registered
    FROM public.journal
    GROUP BY course_id
)
SELECT 
    c.name, 
    t.topic_name AS topic, 
    u.name || ' '|| u.surname AS TEACHER, 
    ((c.end_date - c.start_date) / 7)::int AS duration,
    -- 无报名的课程返回0而非NULL
    COALESCE(csc.students_registered, 0) AS students_registered
FROM public.course c 
RIGHT JOIN public.topic t ON  c.topic_id = t.topic_id 
RIGHT JOIN public.teacher_course tc ON  c.course_id = tc.course_id 
RIGHT JOIN public.user u ON tc.teacher_id = u.user_id 
-- 关联预统计的报名人数
LEFT JOIN course_student_count csc ON c.course_id = csc.course_id
WHERE u.role_id = 2;

方案2：直接在原查询基础上添加GROUP BY #

在原有查询中关联journal表后，把所有非聚合字段对应的原始字段添加到GROUP BY子句即可：

SELECT 
    c.name, 
    t.topic_name AS topic, 
    u.name || ' '|| u.surname AS TEACHER, 
    ((c.end_date - c.start_date) / 7)::int AS duration,
    COUNT(j.id_record) AS students_registered
FROM public.course c 
RIGHT JOIN public.topic t ON  c.topic_id = t.topic_id 
RIGHT JOIN public.teacher_course tc ON  c.course_id = tc.course_id 
RIGHT JOIN public.user u ON tc.teacher_id = u.user_id 
-- 关联报名记录表
LEFT JOIN public.journal j ON c.course_id = j.course_id
WHERE u.role_id = 2
-- 所有非聚合的原始字段都需要加入GROUP BY
GROUP BY c.name, t.topic_name, u.name, u.surname, c.end_date, c.start_date;

注意事项 #

如果一门课程绑定了多名授课教师，以上两种方案都会按教师拆分出多条课程记录，每条记录的报名人数为该课程的总报名数。如果需要按课程维度去重，可根据业务需求调整teacher_course表的关联逻辑。

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