创建日期：2026-09-08 ｜ 最近更新：2026-09-08 本机 MySQL 8.4.0 实测；
shop样例库（300 顾客 / 5000 订单）输出均为真实结果。
MySQL 复习 2：查询进阶——JOIN、聚合、子查询
单表 CRUD 只是热身。真实需求几乎都是「跨表把数据拼起来 + 分组统计」。这篇讲清楚 JOIN 三种、GROUP BY/HAVING、子查询——它们占日常查询的 80%。
1. JOIN：把两张表按「键」拼起来
四张表的关联靠外键：orders.customer_id → customers.id。想查「每笔订单是谁下的」，就把订单行和顾客行按 customer_id 对上：
SELECT o.id, o.amount, c.name, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id -- INNER JOIN：两边都匹配才算
LIMIT 5;
JOIN 语义（最容易混的三兄弟）：
| JOIN | 结果 | 用途 |
|---|---|---|
INNER JOIN（默认 JOIN） | 两边都匹配的行 | 最常见 |
LEFT JOIN | 左表全保留，右表没有就 NULL | 「主表全要，附带信息可有可无」 |
RIGHT JOIN | 右表全保留（可改写成 LEFT，少用） | — |
CROSS JOIN | 笛卡尔积（无 ON） | 很少用，小心爆炸 |
给表和列起短别名（
o/c），长 SQL 才读得动；多表查同名列必须带表前缀（o.id），否则歧义报错。
2. 聚合：GROUP BY 分组 + 聚合函数统计
聚合函数把一组行算成一个数：COUNT(个数) / SUM(和) / AVG(均值) / MIN / MAX。和 GROUP BY 一起用，就是「按某列分组后每组统计」。
SELECT c.city,
COUNT(*) AS orders, -- 该城市订单数
ROUND(SUM(o.amount),2) AS total, -- 该城市订单总额
ROUND(AVG(o.amount),2) AS avg_amt -- 客均价
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid' -- 先过滤，再分组
GROUP BY c.city
ORDER BY total DESC
LIMIT 4;
真实输出：
+--------+--------+----------+-------+
| city | orders | total | avg |
+--------+--------+----------+-------+
| 广州 | 417 | 27071.13 | 64.92 |
| 杭州 | 416 | 27020.08 | 64.95 |
| 北京 | 417 | 27016.29 | 64.79 |
+--------+--------+----------+-------+
顺序铁律（写成一行也别乱）：WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
WHERE：分组前过滤行；HAVING：分组后过滤组（能对聚合结果过滤）；SELECT里非聚合列，基本都要出现在GROUP BY（或用了函数包裹），否则 MySQL 虽然默认放行但结果语义危险。
HAVING 的典型用法：筛「不达标」的组
-- 只留下订单数 >= 20 的顾客里消费最高的 3 个
SELECT c.name,
COUNT(o.id) AS n,
ROUND(SUM(o.amount),2) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING n >= 20
ORDER BY total DESC
LIMIT 3;
真实输出（本组数据没有单顾客订单数 ≥20 的，故结果为空集——HAVING 把他们都滤掉了，这正说明它生效）：
（空结果集：没有任何顾客订单数 ≥ 20，被 HAVING 滤除）
想让它有结果就把阈值调低（
HAVING n >= 2）。HAVING 用在哪、WHERE 用在哪，是高频考点。
3. 子查询：把一张「查询结果」当表用
子查询 = 括号里的 SELECT，结果可当值、当 IN 列表、当临时表。
-- ① 当 IN 列表：找「有 10 单以上已发货订单」的顾客
SELECT name, city FROM customers
WHERE id IN (
SELECT customer_id FROM orders
WHERE status = 'shipped'
GROUP BY customer_id
HAVING COUNT(*) > 10
)
LIMIT 3;
真实输出：
+----------+--------+
| name | city |
+----------+--------+
| 用户3 | 深圳 |
| 用户7 | 上海 |
| 用户11 | 成都 |
+----------+--------+
-- ② 当临时表：统计每城市的「平均消费」
SELECT t.city, ROUND(AVG(t.amount),2) AS city_avg
FROM (SELECT c.city, o.amount
FROM orders o JOIN customers c ON c.id=o.customer_id
WHERE o.status='paid') t
GROUP BY t.city
ORDER BY city_avg DESC
LIMIT 3;
子查询能做但先用 JOIN 想一遍：多数「IN + 子查询」能改写成 JOIN，而且 JOIN 往往更快、可读性也更好。能 JOIN 优先 JOIN。
4. 其它高频「零件」
SELECT DISTINCT city FROM customers; -- 去重
SELECT COALESCE(city, '未知') FROM customers; -- NULL 给默认值
SELECT CASE
WHEN amount >= 100 THEN '大单'
WHEN amount >= 30 THEN '中单'
ELSE '小单'
END AS level FROM orders LIMIT 3;
SELECT CONCAT(c.name, '@', c.city) FROM customers c LIMIT 2; -- 拼接
SELECT DATE_FORMAT(created_at, '%Y-%m') FROM orders LIMIT 2; -- 日期格式化
COALESCE(a,b,c)：返回第一个非 NULL——比到处IFNULL好用；CASE WHEN … THEN … ELSE … END：SQL 里的 if/else，统计口径常用它。
5. 写查询的建议顺序（新手防呆）
- 先想清要哪些表 → 确定 JOIN 和 ON；
- WHERE 先缩行 → 再 GROUP BY 统计；
- 聚合后再 HAVING 筛组；
- ORDER BY 排序、LIMIT 限量；
- 先在小数据/测试库跑通，再放生产。
动手（用 shop）
- 统计每种
status的订单数与总额； - 找消费总额最高的前 5 位顾客（join + group + order + limit）；
- 统计「每个商品被下单多少次」（join order_items/products + group）；
- 用 HAVING 找「下单 ≥ 2 次的顾客」。
自测
- INNER / LEFT JOIN 结果差在哪？
- WHERE 和 HAVING 的执行时机区别？
- SELECT 里出现非聚合、非 GROUP BY 的列，危险在哪？
- 子查询 vs JOIN，一般推荐哪个？
COALESCE干什么的？
下一篇：索引与 EXPLAIN——为什么有的查询秒回、有的全表扫。