综合查询实战

前几篇分别介绍了基础查询（04-检索数据）、条件过滤（05-数据过滤）、聚集函数与分组（08-SQL聚集函数）、连接（03-常用SQL标准）。本文把这些能力组合起来，用一套完整的案例数据演示真实业务查询的写法，并系统理解 SQL 各子句的协作顺序。

准备数据

为便于演示，使用一套"学生 + 班级"的完整案例：

students（学生表）：

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95
3	1	小军	M	88
4	1	小米	F	73
5	2	小白	F	81
6	2	小兵	M	55
7	2	小林	M	85
8	3	小新	F	91
9	3	小王	M	89
10	3	小丽	F	85

classes（班级表）：

id	name
1	一班
2	二班
3	三班
4	四班

建表与数据：

sql

CREATE TABLE classes (
    id   BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE students (
    id       BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name     VARCHAR(100) NOT NULL,
    gender   VARCHAR(1) NOT NULL,
    score    INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基础的单表查询（SELECT 列、DISTINCT、别名、表达式）见《04-检索数据》；WHERE 运算符与优先级见《05-数据过滤》；COUNT/SUM/AVG/MAX/MIN 与 GROUP BY/HAVING 见《08-SQL聚集函数》。本文重点演示它们的组合。

单表综合查询

多条件 + 排序 + 分页

sql

-- 查询一班分数大于 80 的男生，按分数降序，取前 3 条
SELECT id, name, score
FROM students
WHERE class_id = 1 AND score > 80 AND gender = 'M'
ORDER BY score DESC
LIMIT 3;

分组 + 过滤 + 排序

sql

-- 按班级和性别分组，统计人数，只保留人数大于 1 的组，按人数降序
SELECT class_id, gender, COUNT(*) AS num
FROM students
GROUP BY class_id, gender
HAVING COUNT(*) > 1
ORDER BY num DESC;

执行顺序再理解

sql

-- 一条完整查询的书写顺序
SELECT 列, 聚合
FROM 表
WHERE 行过滤
GROUP BY 分组
HAVING 组过滤
ORDER BY 排序
LIMIT 分页;

逻辑执行顺序：FROM → WHERE（过滤行）→ GROUP BY（分组）→ HAVING（过滤组）→ SELECT（投影/聚合）→ DISTINCT → ORDER BY → LIMIT。

多表连接查询

连接方式回顾

多表连接基础（INNER/LEFT/RIGHT/FULL JOIN、ON/USING）详见《03-常用SQL标准》，这里演示连接与过滤、分组的组合。

内连接 + 条件

sql

-- 查询每个学生的姓名和班级名（连接 + 条件过滤）
SELECT s.id, s.name, s.score, c.name AS class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE s.score >= 80;

连接 + 分组 + 聚合

sql

-- 统计每个班级的学生数和平均分（连接 + 分组 + 聚合）
SELECT c.name AS class_name,
       COUNT(*)        AS student_count,
       AVG(s.score)    AS average_score
FROM students s
INNER JOIN classes c ON s.class_id = c.id
GROUP BY c.id, c.name
ORDER BY average_score DESC;

左连接：找出无匹配

sql

-- 四班没有学生：LEFT JOIN 时四班的 count 为 0
SELECT c.name AS class_name, COUNT(s.id) AS student_count
FROM classes c
LEFT JOIN students s ON s.class_id = c.id
GROUP BY c.id, c.name
ORDER BY student_count;

注意 COUNT(s.id) 而不是 COUNT(*)：左连接下无匹配行为 NULL，COUNT(列) 会忽略 NULL，从而正确统计出 0。

自连接

同一张表与自己连接，常用于层级数据（如员工上下级）：

sql

-- 假设 students 有 leader_id 指向同表另一行的 id（班长）
SELECT a.name AS student, b.name AS leader
FROM students a
LEFT JOIN students b ON a.leader_id = b.id;

子查询组合

子查询基础见《09-子查询》，这里演示子查询与主查询的组合。

标量子查询作为条件

sql

-- 查询分数高于全校平均分的学生
SELECT name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);

子查询作为临时表

sql

-- 每个班级平均分最高的班级信息（子查询做聚合，外层再过滤）
SELECT * FROM (
    SELECT class_id, AVG(score) AS avg_score
    FROM students
    GROUP BY class_id
) t
WHERE t.avg_score > 85;

完整业务案例

把多表连接、过滤、分组、聚合、排序组合成一套"班级成绩分析"：

sql

-- 需求：统计每个班级中，分数 >= 80 的男女生人数，
--       只保留总人数 >= 1 的班级，按总分降序输出班级名、性别、人数、平均分
SELECT
    c.name             AS class_name,
    s.gender,
    COUNT(*)           AS cnt,
    AVG(s.score)       AS avg_score
FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE s.score >= 80
GROUP BY c.id, c.name, s.gender
HAVING COUNT(*) >= 1
ORDER BY SUM(s.score) DESC;

这条查询各子句的协作：

子句	作用	顺序
`FROM + JOIN`	关联学生与班级	1
`WHERE`	先过滤掉低分学生	2
`GROUP BY`	按班级+性别分组	3
`HAVING`	过滤不满足的分组	4
`SELECT`	投影列并聚合	5
`ORDER BY`	排序	6

常见组合查询模式

模式 1：Top-N（每组前 N 条）

sql

-- 每个班级分数前 2 名的学生（用窗口函数，见《11-窗口函数》）
SELECT * FROM (
    SELECT s.name, c.name AS class_name, s.score,
           ROW_NUMBER() OVER (PARTITION BY s.class_id ORDER BY s.score DESC) AS rn
    FROM students s
    JOIN classes c ON s.class_id = c.id
) t
WHERE t.rn <= 2;

模式 2：占比/排名统计

sql

-- 每个班级人数占总人数比例
SELECT
    c.name AS class_name,
    COUNT(*) AS cnt,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM students s
JOIN classes c ON s.class_id = c.id
GROUP BY c.id, c.name;

模式 3：去重 / 找未出现

sql

-- 没有学生的班级（用 NOT EXISTS，见《09-子查询》）
SELECT c.name FROM classes c
WHERE NOT EXISTS (
    SELECT 1 FROM students s WHERE s.class_id = c.id
);

总结

查询子句协作顺序

code

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

关键点

单表：WHERE 过滤行 → GROUP BY 分组 → HAVING 过滤组 → ORDER BY 排序 → LIMIT 分页；
多表：先 JOIN 关联，再按上面顺序处理；连接列通常走索引；
组合：COUNT(列) 与 COUNT(*) 在左连接下语义不同（忽略 NULL）；
子查询：标量（单值）用于条件，派生表（多行多列）用于外层再查；
进阶：每组 Top-N、占比、窗口函数与 CTE 组合见《11-窗口函数》《12-CTE公用表表达式》；
理解执行顺序是写对复杂查询的关键：WHERE 不能用 SELECT 别名、HAVING 必须在分组后。