查询所有班级及对应学生数（含0学生班级）的SQL问题

表结构与测试数据 #

class表 #

DROP TABLE IF EXISTS "class";
CREATE TABLE class(
  class_id SERIAL PRIMARY KEY,
  class_name VARCHAR
);


INSERT INTO class (class_name) VALUES ('A');
INSERT INTO class (class_name) VALUES ('B');
INSERT INTO class (class_name) VALUES ('C');

student表 #

DROP TABLE IF EXISTS "student";
CREATE TABLE student(
  student_id SERIAL PRIMARY KEY,
  student_name VARCHAR,
  class_id numeric
);

INSERT INTO student (student_name, class_id) VALUES ('Mike', 1);
INSERT INTO student (student_name, class_id) VALUES ('Jessica', 1);
INSERT INTO student (student_name, class_id) VALUES ('Thomas', 1);

INSERT INTO student (student_name, class_id) VALUES ('Jacob', 2);
INSERT INTO student (student_name, class_id) VALUES ('Izabella', 2);

当前查询存在的问题 #

当前查询语句无法显示学生数为0的班级C：

SELECT class_name, student.class_id, COUNT(DISTINCT student_id) AS num_students
FROM student
LEFT JOIN class ON student.class_id = class.class_id
GROUP BY student.class_id, class.class_name;

当前查询结果 #

期望结果 #

解决方案 #

问题根源是关联顺序错误和分组字段选择不当：

1. 必须以class表作为主表进行左连接，才能保留所有班级记录，包括无学生的班级。
2. 分组需基于class表的字段，而非student表的字段（无学生班级的student.class_id为NULL，会被排除或单独分组）。
3. 统计学生数时，使用COUNT(student.student_id)自动忽略NULL值，无学生时返回0。

正确的查询语句：

SELECT 
    c.class_name, 
    c.class_id, 
    COUNT(s.student_id) AS num_students
FROM class c
LEFT JOIN student s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name;

若要更明确处理NULL场景，可使用COALESCE函数（COUNT本身已返回0，此为可选优化）：

SELECT 
    c.class_name, 
    c.class_id, 
    COALESCE(COUNT(s.student_id), 0) AS num_students
FROM class c
LEFT JOIN student s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name;

关键说明 #

• 以class为左表执行LEFT JOIN，确保所有班级都被纳入结果集。
• 关联条件绑定class和student的class_id，匹配对应班级的学生。
• COUNT(s.student_id)仅统计非NULL的学生ID，无学生时自然返回0。
• 按class表的class_id和class_name分组，保证每个班级作为独立分组统计。

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