我有两张桌子:class张和student张.我想得到所有现有的班级，并得到学生的数量(计数)每个给定的班级.诀窍是，我还想展示一个有0名学生的班级.
表"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);
我的查询可以工作，但NOT是否显示了C 班，其中有0名学生:
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;
上面的查询给出了以下结果:
| class_name | class_id | num_students |
|---|---|---|
| A | 1 | 3 |
| B | 2 | 2 |
我很希望能够得到这样的东西:
| class_name | class_id | num_students |
|---|---|---|
| A | 1 | 3 |
| B | 2 | 2 |
| C | 3 | 0 |