# 子查询：把先算出来的那个数交给 WHERE 用 为什么 WHERE 里不能直接放 AVG、标量子查询和 IN 子查询各自接什么形状的结果，以及括号里的查询为什么看不见外层的条件 > 子查询 · 聚合函数 · WHERE · 约 7 分钟 · 11 月 01 日 ## 本篇要点 1. 一条查询内部先执行 FROM，再执行 WHERE，聚合在 WHERE 之后才算，所以 WHERE 里不能直接放 AVG 这类聚合函数，MySQL 会报 1111 Invalid use of group function。 2. 把带聚合的查询放进括号当成一个值，就能写出“金额高于平均值”这类条件：括号里的查询先独立跑一遍，外层再拿结果逐行比较。 3. 只返回一行一列的子查询叫标量子查询，只能放在 > = < 的右边；多返回一行就报 ERROR 1242。 4. 不带 GROUP BY 的聚合函数永远返回一行，因此它是标量子查询最稳妥的来源。 5. 括号里返回一列多行时改用 IN；IN 只判断“在不在集合里”，名单里有重复也不会像 JOIN 那样把外层行数放大。 6. 子查询是自足的，看不到外层的 WHERE 条件；要它只算杭州的平均值，必须在自己里面也写 city = '杭州'。 7. 动手前的检查动作：把括号里的查询单独跑一次，确认它到底返回几行几列。 --- 前面四篇，`WHERE` 后面跟的都是你自己写死的条件：`amount > 100`、`city = '杭州'`。条件里的数你事先知道。 现在换一个问题：**哪些订单的金额高于所有订单的平均值？** 这道题不一样了。条件里的那个数，你自己都不知道是多少，得先让数据库算出来。这一篇就讲这一件事：当筛选条件本身需要一个查询结果时，怎么把两条查询串成一条。 ## 先把数据摆出来 `orders` 表还是前面那张，9 行： | order_id | user_id | city | amount | |---|---|---|---| | 1001 | 8823 | 杭州 | 129.00 | | 1002 | 9107 | 上海 | 58.50 | | 1003 | 8823 | 杭州 | 320.00 | | 1004 | 9310 | NULL | 76.00 | | 1005 | 9107 | 杭州 | 45.00 | | 1006 | 9107 | 上海 | 210.00 | | 1007 | 9310 | 上海 | 91.50 | | 1008 | 8823 | NULL | 19.90 | | 1009 | 9500 | 北京 | 199.00 | 先手算一遍，后面才好核对：九笔加起来是 1148.90，除以 9，平均值 $1148.90 \div 9 \approx 127.66$。所以“高于平均值”的应该是 129.00、320.00、210.00、199.00 这四笔。 ## 卡住的地方：条件里的数要先算 最直接的想法是分两步：先单独跑一条 `SELECT AVG(amount) FROM orders;` 拿到 127.66，再把它填进条件 `WHERE amount > 127.66`。结果确实对，但这个做法站不住脚——明天新增一笔订单，平均值就不是 127.66 了，条件立刻过期；每换一个时间范围，你都得再手工抄一遍。 你想的是：能不能让数据库自己先算平均值，再拿这个结果去筛？答案是可以，但写法有讲究。 ## 为什么不能直接写成 `WHERE amount > AVG(amount)` 很多人第一次会这么写，然后收到一条报错： ```sql SELECT order_id, city, amount FROM orders WHERE amount > AVG(amount); -- ERROR 1111 (HY000): Invalid use of group function ``` “Invalid use of group function” 的意思是：聚合函数用错了地方 [1]。要理解为什么错，得回想一条查询内部的执行顺序： $$\text{FROM} \rightarrow \text{WHERE} \rightarrow \text{GROUP BY 与聚合} \rightarrow \text{HAVING} \rightarrow \text{SELECT} \rightarrow \text{ORDER BY}$$ 关键在这两段的先后：**`WHERE` 是逐行判断，它跑的时候聚合还根本没开始算。** 数据库手里只有一行行的订单，没有“一组数”，`AVG(amount)` 无从下手。这跟第三篇里 `HAVING` 能放聚合函数是同一件事的两面——`HAVING` 站在聚合之后，`WHERE` 站在聚合之前。顺带说一句，这里的 `HAVING` 也帮不上忙：它筛的是“组”，而这道题要留下的是单笔订单。 ## 办法：把算平均值的那条语句放进括号 真正要用的是这个写法： ```sql SELECT order_id, city, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders); ``` 括号里的 `(SELECT AVG(amount) FROM orders)` 就是一个**子查询**：一条完整的 `SELECT`，被当成一个值嵌进另一条语句里。 它的执行方式和你想的一致：括号里那条先独立跑一次，得到一个数 127.66；然后外层拿这个 127.66 当成普通数字，逐行判断 `amount > 127.66`。所以结果和手算一致： | order_id | city | amount | |---|---|---| | 1001 | 杭州 | 129.00 | | 1003 | 杭州 | 320.00 | | 1006 | 上海 | 210.00 | | 1009 | 北京 | 199.00 | 1001 的 129.00 只比 127.66 高一点点，照样进；1007 的 91.50 离平均线还差一截，进不来。这类“刚好跨过线”的行，是检验条件写对没写对的最好样本。 ## “标量”是什么意思，以及它带来的一个限制 括号里那种只返回**一行一列、也就是一个格子**的子查询，叫**标量子查询**。“标量”就是“单个值”的意思，和你熟悉的 127.66、'杭州' 是同一类东西，区别只是这个值由查询算出来。 为什么值得专门给它一个名字？因为它能放的位置，取决于它返回的形状。`amount > (...)` 里，`>` 的右边只能站一个值。所以： **括号里那条查询只有在你确定它最多返回一行时，才能这样用。** 如果它返回好几行，数据库不知道该拿哪一个去比较，直接报错： ```sql SELECT user_name FROM users WHERE user_id = (SELECT user_id FROM orders); -- ERROR 1242 (21000): Subquery returns more than 1 row ``` 这是最容易踩的一步。`orders.user_id` 有 9 行、8823 还重复三次，所以括号里吐出 9 行，`=` 右边站不住 [2]。 而 `AVG(amount)` 那种写法为什么稳？因为不带 `GROUP BY` 的聚合函数，无论表里有多少行，永远塌成一行。**“聚合 + 没有 GROUP BY”是标量子查询最可靠的来源。** ## 当括号里给出的是一份名单，就该用 IN 换一个问题：**哪些用户下过单？** 这次从 `users` 出发，条件是“这个 user_id 出现在 orders 的那一列里”： ```sql SELECT user_id, user_name FROM users WHERE user_id IN (SELECT user_id FROM orders); ``` 结果是三行：8823 阿May、9107 老周、9310 小林。Tony（9402）一单没下，不在名单里，所以不出现；9500 在名单里，但 `users` 里根本没有这个人，所以也不影响结果。 这里括号里返回的是**一列、九行**——一份名单，不是单个值。`IN` 正好吃这个形状：它问的是“这个值在不在右边这一堆值里”，右边有几个都不妨碍。 一个细节值得留意：名单里 8823 重复了三次，结果并没有变成三行阿May。**`IN` 只做“在不在”的判断，不会一对一把外层行复制出去。** 这和上一篇的 `JOIN` 正好是鲜明对照：同样是“下过单的用户”，用 `JOIN` 拼回三笔订单就是三行，用 `IN` 仍然是一行。 ## 判断该用哪种：只看括号里的形状 这两种写法不是“高级”和“低级”的区别，而是**形状匹配**的问题。写之前先在心里问一句：括号里那条查询会吐出什么？
```mermaid flowchart LR A["先单独跑一遍括号里的查询"] --> B{"返回什么形状？"} B -->|"一行一列：一个值"| C["放到 > = < 右边，当数字用"] B -->|"一列多行：一份名单"| D["放进 IN 的括号里"] C --> E["多返回一行就报 1242"] D --> F["名单有重复也不放大行数"] ```
养成一个习惯：**拿不准时，先把括号里那条语句单独跑一次，看它到底返回几行几列。** 这一步花三秒，能省掉大半的报错。 ## 一个必须知道的地方：括号里的查询看不见外面的条件 这是子查询里最容易悄悄算错的地方。题目改成：**杭州的订单里，哪些金额高于杭州的平均值？** 杭州只有三笔：129.00、320.00、45.00，平均是 $494.00 \div 3 \approx 164.67$。所以正确答案只有 320.00 一笔。 如果你顺手写成这样： ```sql SELECT order_id, city, amount FROM orders WHERE city = '杭州' AND amount > (SELECT AVG(amount) FROM orders); ``` 结果会是两行：129.00 和 320.00。**多出来的 129.00 是错的。** 原因就是前面说的：括号里那条查询是**独立跑的一遍**，它面对的是整张 `orders` 表，算出来是 127.66 这个全表平均，它根本不知道外层还写了 `city = '杭州'`。要让平均值也只算杭州，必须在子查询自己的 `WHERE` 里再写一次条件： ```sql SELECT order_id, city, amount FROM orders WHERE city = '杭州' AND amount > (SELECT AVG(amount) FROM orders WHERE city = '杭州'); ``` 这样括号里算出 164.67，结果只剩 320.00 一笔。 把这件事记住成一句话：**子查询是一个自足的查询，你在它里面没写的条件，它一律不认。** 所以每次用子查询，都要追问一句：括号里那张表，应该被哪些条件限制？ ## 现在你能回答什么 子查询要做的事只有一件：把一条查询的结果交给另一条查询用。写之前先确定括号里的查询会返回什么形状——一个值就放到比较运算符右边，一份名单就放 `IN` 的括号里；再检查括号里该有的筛选条件有没有漏。剩下的，就是多跑几次、拿手算的数对一对。 还有两件相邻的事这一篇没讲：括号里引用外层某一列的值（相关子查询），以及把子查询放进 `FROM` 里当一张临时表来用——后者是下一步把复杂问题拆成几步的关键，留到下一篇。 ## 思维导图 ```mermaid flowchart LR Q["哪些订单金额高于平均值？"] --> P["卡点：条件里的数要先算出来"] P --> E1["直接写 WHERE amount > AVG(amount)"] E1 --> E2["报 1111：WHERE 在聚合之前执行"] P --> S["写成 WHERE amount > (SELECT AVG(amount) FROM orders)"] S --> S1["标量子查询：一行一列一个值"] S1 --> S2["多返回一行就报 1242"] P --> I["换成名单类问题：WHERE user_id IN (SELECT user_id FROM orders)"] I --> I1["IN 只看在不在，不放大行数"] S --> T["坑：括号里看不见外层的 city = '杭州'"] T --> T1["平均值要在子查询自己的 WHERE 里限定"] ``` ## 术语表 - 子查询：一条完整的 SELECT 嵌在另一条语句里，把它的结果交给外层使用。 - 标量子查询：只返回一行一列、也就是单个值的子查询，可以当普通数字放在比较运算符右边。 - IN 子查询：括号里返回一列多行、相当于一份名单，外层用 IN 判断某个值在不在里面。 - 执行顺序：FROM 到 WHERE 再到聚合、HAVING、SELECT 的先后次序，决定了聚合函数为什么不能出现在 WHERE 里。 - 子查询的独立性：括号里的查询对外层条件一无所知，需要什么限制都得在它自己的 WHERE 里重写。 ## 来源 1. [MySQL 8.4 Reference Manual: Subquery Errors — 官方手册列出 ERROR 1241、1242 等子查询错误的触发条件与改写方式](https://dev.mysql.com/doc/refman/8.4/en/subquery-errors.html) 2. [How to fix MySQL invalid use of group function error — 解释 ERROR 1111 的成因：聚合函数不能放在 WHERE 里，以及用子查询或 HAVING 的两种改法](https://sebhastian.com/mysql-invalid-use-group-function/) --- 原文：https://pangzhengboyin.com/articles/sql-subquery-in-where-clause-ac6f5145 > **庞征博引** · 想学的，慢慢都会 > > 庞征博引是把想学的东西写成连载的 AI 学习工具：每篇 5–10 分钟，边读边问，接下来学什么跟着你走。这篇就是这样写出来的。 > > 开始你自己的连载 → https://pangzhengboyin.com