8、高级查询技巧：JOIN连接、子查询与聚合函数
说实话，数据库编程里最考验功力的地方，就是怎么把数据从多个表里“捞”出来。单表查询谁都会写，但一旦涉及到两张甚至更多表，很多人就开始犯晕。我刚开始做嵌入式数据库开发时，也在这上面栽过跟头——有一次因为搞混了 LEFT JOIN 和 INNER JOIN，查出来的数据少了整整一半，排查了整整一个下午。
今天咱们就把这些高级查询技巧掰开揉碎了讲。你想想看，掌握了这些，以后写 SQL 就能像搭积木一样灵活。
8.1 聚合函数：给数据“算总账”
聚合函数说白了就是用来做统计的。我习惯把它们分成两类：一类是数数的（COUNT），一类是算数的（SUM、AVG、MAX、MIN）。
先看个例子。假设我们有个订单表：
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT,
amount REAL,
status TEXT
);
INSERT INTO orders VALUES
(1, '张三', 150.0, '已完成'),
(2, '李四', 200.0, '已完成'),
(3, '张三', 80.0, '待支付'),
(4, '王五', 300.0, '已完成'),
(5, '李四', 120.0, '已取消');
现在我想知道：
- 总共有多少笔订单？
- 已完成订单的总金额是多少？
- 平均每笔订单金额是多少？
用聚合函数写出来就是：
SELECT
COUNT(*) AS 总订单数,
SUM(CASE WHEN status = '已完成' THEN amount ELSE 0 END) AS 已完成总额,
AVG(amount) AS 平均金额,
MAX(amount) AS 最大金额,
MIN(amount) AS 最小金额
FROM orders;
输出结果：
| 总订单数 | 已完成总额 | 平均金额 | 最大金额 | 最小金额 |
|---|---|---|---|---|
| 5 | 650.0 | 170.0 | 300.0 | 80.0 |
避坑指南：我曾经犯过一个低级错误——用 COUNT(amount) 去统计行数。结果发现有一行 amount 是 NULL，COUNT 直接把它跳过了。记住，COUNT(*) 统计所有行，COUNT(列名) 只统计非 NULL 的行。
8.2 GROUP BY：分组统计的艺术
光有聚合函数还不够。很多时候我们需要按某个维度分组统计。比如按客户统计每个人的订单总额：
SELECT
customer,
COUNT(*) AS 订单数,
SUM(amount) AS 总金额
FROM orders
GROUP BY customer;
输出：
| customer | 订单数 | 总金额 |
|---|---|---|
| 张三 | 2 | 230.0 |
| 李四 | 2 | 320.0 |
| 王五 | 1 | 300.0 |
嗯，这里要注意：SELECT 子句中出现的非聚合列，必须出现在 GROUP BY 中。这是 SQL 的硬性规定，违反就会报错。
8.3 HAVING：对分组结果“再过滤”
WHERE 是在分组前过滤，HAVING 是在分组后过滤。比如我只想看订单数超过1的客户：
SELECT
customer,
COUNT(*) AS 订单数
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;
结果只返回张三和李四，王五被过滤掉了。
重要提醒：HAVING 和 WHERE 的执行顺序不同。WHERE 先执行，过滤掉不需要的行；然后 GROUP BY 分组；最后 HAVING 过滤分组结果。这个顺序搞错了，查询结果可能完全不对。
8.4 JOIN连接：把表“粘”在一起
实际项目中数据很少只存在一张表里。比如订单表只存客户ID，客户详细信息在另一张表。这时候就需要 JOIN。
先建两张表：
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
city TEXT
);
CREATE TABLE orders2 (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount REAL,
order_date TEXT
);
INSERT INTO customers VALUES
(1, '张三', '北京'),
(2, '李四', '上海'),
(3, '王五', '广州');
INSERT INTO orders2 VALUES
(101, 1, 150.0, '2024-01-15'),
(102, 2, 200.0, '2024-01-16'),
(103, 1, 80.0, '2024-01-17'),
(104, 4, 300.0, '2024-01-18'); -- 注意：客户ID=4 在customers表中不存在
8.4.1 INNER JOIN：只取交集
INNER JOIN 只返回两个表中匹配的行。说白了就是“两边都有才算数”：
SELECT
c.name,
c.city,
o.amount,
o.order_date
FROM customers c
INNER JOIN orders2 o ON c.id = o.customer_id;
结果只有3行：张三2笔，李四1笔。王五没有订单所以不出现，客户ID=4的订单因为customers表里没有对应记录也不出现。
8.4.2 LEFT JOIN：左表全保留
LEFT JOIN 会保留左表的所有行，右表没有匹配的用 NULL 填充。这个我用的最多，因为很多时候需要知道“哪些数据没有关联上”：
SELECT
c.name,
c.city,
o.amount,
o.order_date
FROM customers c
LEFT JOIN orders2 o ON c.id = o.customer_id;
结果有4行：张三2笔，李四1笔，王五1笔（amount和order_date为NULL）。
我的经验：调试 JOIN 查询时，我习惯先分别查两张表的数据量，再对比 JOIN 后的结果。如果行数对不上，十有八九是 JOIN 条件写错了。有一次我排查了半天，发现是 ON 条件里用了 c.id = o.id，而不是 c.id = o.customer_id——这种低级错误，写代码时真的很容易忽略。
8.5 子查询：查询里套查询
子查询就是 SQL 里再嵌套一个 SQL。我把它分成两种：
- 标量子查询：返回单个值，可以用在 SELECT、WHERE 里
- 表子查询：返回多行多列，通常用在 FROM 或 IN 里
举个例子，找出订单金额高于平均值的订单：
SELECT id, customer_id, amount
FROM orders2
WHERE amount > (SELECT AVG(amount) FROM orders2);
这里 (SELECT AVG(amount) FROM orders2) 就是一个标量子查询，先算出平均值，再拿每行去比较。
再看一个表子查询的例子：找出每个客户的最新一笔订单：
SELECT c.name, o.amount, o.order_date
FROM customers c
INNER JOIN (
SELECT customer_id, amount, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders2
) o ON c.id = o.customer_id AND o.rn = 1;
性能提示：子查询不是万能的。我遇到过嵌套了四五层的子查询，跑起来慢得像蜗牛。后来改成 JOIN 或者临时表，速度提升了十几倍。记住：能用 JOIN 解决的，尽量别用子查询。
8.6 综合实战：一个完整的查询
把今天学的串起来，写一个稍微复杂点的查询：统计每个城市已完成订单的总金额，只显示总金额超过100的城市，按金额降序排列。
SELECT
c.city,
COUNT(o.id) AS 订单数,
SUM(o.amount) AS 总金额
FROM customers c
LEFT JOIN orders2 o ON c.id = o.customer_id
WHERE o.status = '已完成' OR o.status IS NULL
GROUP BY c.city
HAVING SUM(o.amount) > 100
ORDER BY 总金额 DESC;
这个查询里，LEFT JOIN、WHERE、GROUP BY、HAVING、ORDER BY 全用上了。你想想看，如果不用这些高级技巧，光靠单表查询，得写多少代码才能实现同样的功能？
8.7 知识体系总览
下面这张图把今天讲的内容串了起来，方便你复习时快速回忆：
嗯，今天的内容就到这里。这些高级查询技巧，说白了就是让你能用更少的代码，做更复杂的数据分析。刚开始可能会觉得有点绕，但多写几次就熟练了。记住：先理解数据之间的关系，再写 SQL，这样能少走很多弯路。
公众号:蓝海资料掘金营，微信deep3321