「花得比平均多的用户」：子查询三个位置

老板：「找出消费高于平均水平的那批人，我给他们发券。」平均值本身就要一条查询来算--你第一次需要查询里套查询。

学 40 min

练 65 min

盘 15 min

共 120 分钟

完成这一天

学 · 40 min

01标量子查询（SELECT 里）：必须只返回一行一列
场景「消费高于平均水平」--平均水平这个数，得先用一条查询算出来。
返回一行一列的子查询叫标量子查询，可以放在任何「单个值」能放的位置：SELECT 列表里、比较运算符右边。它先算一次，结果当一个常量用。
```
select user_id,
       sum(total_amount) as 消费额,
       sum(total_amount) - (select avg(total_amount) from orders) as 高于平均多少
from orders
group by user_id;
```
易错子查询返回两行就报错 more than one row returned--而且这是运行时才炸：测试数据少时潜伏，上线数据多了才炸。
02派生表（FROM 里）：必须起别名
场景「每个用户的订单数 + 用户名」--订单数要先聚合，users 的名字在另一张表，两步走。
from (子查询) t：先把子查询算成一张临时表，再对这张表连接、过滤。PG 强制要求给这个临时表起别名。它是「先聚合再连接」套路的标准载体--凡是你想说「先把 X 算出来，再和 Y 拼」，就是派生表。
```
select u.name, t.单数
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t
join users u on u.id = t.user_id;
```
易错忘写别名是语法错；别名和真实表撞名，连接时列的归属会看不清。
03条件子查询（WHERE 里）：单值与多值
场景「有过已支付记录的订单」--过滤条件本身是一张查询的结果。
用哪个运算符，取决于子查询吐出什么形状：一行一列配 = <>；一列多行配 IN / NOT IN；多列的极少（配行构造器）。写之前先问自己：这个子查询大概返回几行几列？
```
select *
from orders
where user_id in (select user_id from orders where status = 2);
-- 单值版：= (select avg(total_amount) from orders)
```
易错ANY / ALL 也接多值一列（如 > ANY 表示「大于其中任意一个」），知道存在即可，可读性差、少用。
04相关子查询：什么时候会被执行 N 次
场景同一条 SQL，小表上秒出，大数据量上突然慢几十倍--先怀疑它。
判断标准：子查询里引用了外层的列（如 o.user_id）就是相关子查询。逻辑上外层每行都要带着自己的值进子查询算一遍，N 行 = N 次。非相关子查询只算一次、结果当常量。明天 D17 整天跟它打交道。
```
-- 相关：每个用户和"他自己的"平均客单价比
select id, total_amount
from orders o
where total_amount >= (select avg(total_amount)
                       from orders o2
                       where o2.user_id = o.user_id);
```
易错EXPLAIN 里看到 SubPlan 出现在大行数节点之下，就是「每行执行一次」的现场，先估一下总行数 × 子查询成本。

练 · 65 min

用标量子查询输出「每个用户消费额 − 全站平均消费额」

参考答案

口径先说清：「全站平均消费额」的分母是用户数（先按用户聚合再 avg），不是订单数。标量子查询是非相关的，整条 SQL 里只算一次、结果当常量用。

select user_id,
       sum(total_amount) as 消费额,
       round(sum(total_amount)
             - (select avg(消费额)
                from (select user_id, sum(total_amount) as 消费额
                      from orders group by 1) x), 2) as 与全站平均的差额
from orders
group by 1;

用派生表先按用户聚合订单数，再连接 users 输出
参考答案

派生表必须起别名（PG 强制），它是「先聚合再连接」套路的标准载体：先把订单数算成一张临时表，再去 users 拿名字。
```
select u.name, t.单数
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t
join users u on u.id = t.user_id;
```
用 WHERE id IN (子查询) 查有过已支付记录的订单
参考答案

子查询返回一列多行，所以配 IN。写之前先问「子查询吐出什么形状」：一行一列配 =，一列多行配 IN / NOT IN。
```
select *
from orders
where user_id in (select user_id from orders where status = 2);
```
写一个返回多行的标量子查询，记录报错信息
参考答案

已取消的订单远不止一单，标量位置的子查询返回多行直接报错。注意这是运行时才炸：测试数据少时可能恰好一行、潜伏到上线。改成 in (...) 就是合法写法。
```
select id, total_amount
from orders
where total_amount = (select total_amount from orders where status = 3);
-- ERROR: more than one row returned by a subquery used as an expression
```

同一需求分别用派生表和 CTE 写，对比可读性

参考答案

两层结果完全一致，优化器眼里也是同一个东西；差别在读法。这题只有一层，差异不大--嵌到三层时 CTE 从上往下读的优势会碾压式显现，明天整天都在写它。

-- 派生表版：从外往里读，数括号
select u.name, t.单数
from (select user_id, count(*) as 单数 from orders group by 1) t
join users u on u.id = t.user_id;

-- CTE 版：从上往下读，先定义后使用
with 每用户单数 as (
  select user_id, count(*) as 单数 from orders group by 1
)
select u.name, t.单数
from 每用户单数 t
join users u on u.id = t.user_id;

过关标准 能说出标量子查询在什么情况下会被执行 N 次（相关子查询）。

学 · 40 min

01标量子查询（SELECT 里）：必须只返回一行一列

02派生表（FROM 里）：必须起别名

03条件子查询（WHERE 里）：单值与多值

04相关子查询：什么时候会被执行 N 次

练 · 65 min

出题端点设置