1
SELECT class_id, COUNT(*) AS 人数
2
FROM student
3
GROUP BY class_id;

1
SELECT class_id, COUNT(*) AS 人数
2
FROM student
3
WHERE age >= 18            -- 先过滤出成年的学生
4
GROUP BY class_id
5
HAVING COUNT(*) > 3;       -- 再筛出人数超过 3 的班级

1
SELECT class_id, gender, COUNT(*) AS 人数
2
FROM student
3
GROUP BY class_id, gender;

1
SELECT class_id, COUNT(*) AS 人数
2
FROM student
3
GROUP BY class_id WITH ROLLUP;

1
-- 列子查询：查一班所有学生的姓名
2
SELECT name FROM student
3
WHERE class_id IN (SELECT id FROM class WHERE name = '一班');
4

5
-- 标量子查询：查年龄大于平均年龄的学生
6
SELECT name FROM student
7
WHERE age > (SELECT AVG(age) FROM student);

1
SELECT t.class_id, t.人数
2
FROM (SELECT class_id, COUNT(*) AS 人数 FROM student GROUP BY class_id) AS t
3
WHERE t.人数 > 3;

1
SELECT name, (SELECT name FROM class c WHERE c.id = s.class_id) AS 班级
2
FROM student s;

1
SELECT c.name
2
FROM class c
3
WHERE EXISTS (
4
    SELECT 1 FROM student s WHERE s.class_id = c.id
5
);