场景 类目是棵树：「电子 > 手机 > 配件」。树要怎么塞进一张表？

讲解 每行一个类目，加一列 parent_id 指向本表里父类目的 id，顶级类目的 parent_id 为 NULL。这叫邻接表模型：树结构不需要多张表，一列自引用就够，而且加层级不用改表结构。

-- 表里长这样：父和子是同一张表里的行
select id, name, parent_id from categories order by id limit 6;

易错 树的深度没有上限。任何「固定跳两层 JOIN」的写法都偷偷假设了「树只有两层」--需求一说「所有层级」就得换递归（D18）。

场景 要输出「一级类目名 | 二级类目名」对照表，可父和子在同一张表里。

讲解 同一张表在 FROM 里出现两次、起两个别名（c 演父、s 演子），连接条件写 s.parent_id = c.id。对数据库来说是两个独立的数据源，对你来说是同一张表的两个角色--这就是自连接的全部秘密。

select c.name as 一级类目, s.name as 二级类目
from categories c
join categories s on s.parent_id = c.id
order by 1, 2;

易错 方向别写反：c.parent_id = s.id 查出来的是「父挂子名」，全表错位但不报错。

场景 拿到一棵树，日常打交道的就是这三个动作。

讲解 ① 平铺：直接 select *，树形靠 parent_id 脑补；② 按一级分组：先筛出顶级类目（parent_id is null），再自连接挂上孩子；③ 查某类目的直接孩子：where parent_id = 某id，只下一层。

-- ② 一级类目和它的直接孩子
select c.name as 一级, s.name as 孩子
from categories c
join categories s on s.parent_id = c.id
order by 1;

-- ③ 「手机」的直接孩子
select id, name from categories
where parent_id = (select id from categories where name = '手机');

易错 「按一级分组」要先问一句：商品的 category_id 挂在几级？可能挂二级、可能直接挂一级，路径不定就是递归的活。

场景 老板要按一级类目看 GMV。order_items 里只有 product_id，商品的 category_id 还未必是顶级。

讲解 连接链四步：order_items ->（product_id）products 拿 category_id ->（category_id）categories 第一次进，拿到二级类目 ->（parent_id）categories 第二次进，拿到一级类目的名字。categories 表进两次、两个别名，一个演二级一个演一级。

select c1.name as 一级类目,
       sum(i.qty * i.unit_price) as gmv
from order_items i
join products   p  on p.id = i.product_id
join categories c2 on c2.id = p.category_id
join categories c1 on c1.id = c2.parent_id
group by 1
order by gmv desc;

易错 类目树不止两层时，c2 可能本身就是顶级（parent_id 为 NULL），这些商品的 GMV 会整段消失。先 coalesce 兜底或统一归到根。

场景 「消费高于平均水平」--平均水平这个数，得先用一条查询算出来。

讲解 返回一行一列的子查询叫标量子查询，可以放在任何「单个值」能放的位置：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--而且这是运行时才炸：测试数据少时潜伏，上线数据多了才炸。

场景 「每个用户的订单数 + 用户名」--订单数要先聚合，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;

易错 忘写别名是语法错；别名和真实表撞名，连接时列的归属会看不清。

场景 「有过已支付记录的订单」--过滤条件本身是一张查询的结果。

讲解 用哪个运算符，取决于子查询吐出什么形状：一行一列配 = <>；一列多行配 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 表示「大于其中任意一个」），知道存在即可，可读性差、少用。

场景 同一条 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 出现在大行数节点之下，就是「每行执行一次」的现场，先估一下 总行数 × 子查询成本。

场景 D16 留的尾巴，今天掰开：两种子查询在引擎眼里是两种东西。

讲解 非相关子查询（不引用外层列）算一次当常量，优化器还常把它改写成 hash semi-join，效率不差。相关子查询（引用了外层列）逻辑上每行执行一次--但现代优化器对 EXISTS / IN 也常能改写成 join，别靠猜，靠 EXPLAIN。看计划里是 Hash Semi Join（已改写）还是 SubPlan（真在逐行执行）。

-- 非相关：算一次
select * from users where city = '上海' and id in (select user_id from orders);
-- 相关：EXPLAIN 看它被改写没有
select * from users u
where exists (select 1 from orders o where o.user_id = u.id and o.status = 2);

易错 「IN 慢、EXISTS 快」这句老话在 PG 上早已不一定成立--优化器面前两者常常殊途同归，面试要按版本讲。

场景 查「下过单的用户」：其实只需要为每个用户找到一个证据。

讲解 exists (子查询) 只关心「有没有行返回」：扫到第一行立即返回 TRUE，剩下的全不看。所以子查询里 select 1 还是 select * 完全无所谓--列表根本不会被取值。NOT EXISTS 同理短路。

select u.name
from users u
where exists (select 1
              from orders o
              where o.user_id = u.id);

易错 exists 里别写聚合（子查询的意义是「存在性」，不是算数）；select 1 是惯例，为了读的人一眼看出「这查的是存在性」。

场景 事故通报的还原：NOT IN 查「没下过单的用户」，上线返回空集，运营以为系统挂了。

讲解 一步步推：x NOT IN (a, b, c) 等价于 x<>a AND x<>b AND x<>c。集合里混进一个 NULL，则 x <> NULL 得 UNKNOWN；UNKNOWN 参与 AND，整个表达式永远到不了 TRUE。WHERE 只放行 TRUE --整张表一行都过不了。这不是 bug，是三值逻辑的必然结论（D3 的伏笔今天收了）。

select 2 not in (1, 3, null);   -- 结果是 NULL，不是 true！
select count(*) from users
where id not in (select user_id from orders);   -- user_id 有脏 NULL 时 = 0 行

易错 子查询那一列可能含 NULL（脏数据、外连接结果），就永远别用 NOT IN--用 NOT EXISTS。

场景 「没下过单的用户」已经有两把枪了，加上 NOT EXISTS 是三把--该常备哪把？

讲解 三者在正确性上等价：D10 的 LEFT JOIN + IS NULL、今天的 NOT EXISTS、以及有 NULL 陷阱的 NOT IN。性能上现代优化器常给三者生成相同计划；语义安全上 NOT EXISTS 没有 NULL 陷阱、也不要求右表连接列非空。默认写 NOT EXISTS，另两种能看懂能改写。

select u.name
from users u
where not exists (select 1 from orders o where o.user_id = u.id);

-- 等价的 LEFT JOIN 版（D10）
select u.name
from users u
left join orders o on o.user_id = u.id
where o.id is null;

易错 面试讲这道题的标准动作：先说「NOT IN 有 NULL 陷阱」，再现场推导，最后给出两种安全写法--一气呵成。

场景 「电子」下面所有层级--几层不知道，JOIN 写死了就不行。

讲解 固定模板背下来：WITH RECURSIVE t AS ( 初始项 UNION ALL 递归项 )。初始项（锚点）给出起点：parent_id 指向「电子」的直接孩子；递归项引用 t 自己再往下走一层。执行过程：先算初始项 --> 结果喂给递归项 --> 算出的新行再喂回去 --> 直到没有新行为止。

with recursive sub as (
  select id, name, parent_id from categories where parent_id = 1  -- 初始项：直接孩子
  union all
  select c.id, c.name, c.parent_id
  from categories c
  join sub s on c.parent_id = s.id                                -- 递归项：往下再走一层
)
select * from sub;

易错 递归项只能引用 CTE 自己一次；把 UNION ALL 写成 UNION 会去重，语法没错但轮次语义悄悄变了。

场景 递归没有 while，它怎么知道该停？

讲解 终止条件是隐式的：某一轮递归项产出 0 行新数据，递归就停。没有「超过 N 层就停」的开关--树有多深就递归多深。想看清执行过程，在每行带一个 depth 列：初始项 depth=0，递归项 depth+1，结果按 depth 排就是「一层一层剥开」的现场。

with recursive sub as (
  select id, name, 0 as depth from categories where id = 1
  union all
  select c.id, c.name, s.depth + 1
  from categories c
  join sub s on c.parent_id = s.id
)
select * from sub order by depth;

易错 数据里有环（a 的父是 b、b 的父是 a）时每轮都有新行，递归永远不停--下一条讲怎么防。

场景 脏数据造出环形类目树，一条递归查询把 CPU 跑满。

讲解 两道保险：① 随身携带「已访问路径」数组，递归项里 where not c.id = any(路径)，走过的节点不再走；② 加 depth < 20 硬上限兜底。生产上的递归查询两道都要有。

with recursive sub as (
  select id, name, parent_id, 0 as depth, array[id] as path
  from categories where id = 1
  union all
  select c.id, c.name, c.parent_id, s.depth + 1, s.path || c.id
  from categories c
  join sub s on c.parent_id = s.id
  where not c.id = any(s.path)   -- 环保险
    and s.depth < 20             -- 深度保险
)
select * from sub;

易错 递归跑不停时，从另一个窗口查 pg_stat_activity 能看到它 state = active 一直不动，pg_terminate_backend(pid) 停掉它。

场景 「每个分类的完整路径（电子 > 手机 > 配件）」和「每个分类在第几层」--两个需求一个套路。

讲解 递归项里维护「随身列」：depth = 父.depth + 1；path = 父.path || ' > ' || 本节点名。每行都带着「我是怎么来的」--这是递归 CTE 的万金油模式，碰到任何递归需求先想这两个随身列怎么带。

with recursive tree as (
  select id, name, parent_id, name::text as path, 1 as depth
  from categories where parent_id is null
  union all
  select c.id, c.name, c.parent_id,
         t.path || ' > ' || c.name, t.depth + 1
  from categories c
  join tree t on c.parent_id = t.id
)
select name, path, depth from tree order by path;

易错 路径用 text 拼接，类目名本身含「>」就有歧义；严格场景用数组类型存 path，展示时再 join。

场景 派生表套派生表，三层括号已经读不懂了--需要给每个中间结果起名字。

讲解 with a as (...), b as (...引用 a...), c as (...引用 b...) select ...：后面的能用前面的，自顶向下一条流水线。给每段起业务名（清洗 / 聚合 / 排名），SQL 从一坨嵌套变成分段的作文--CTE 的核心价值是给思路命名。

with 聚合 as (
  select user_id, sum(total_amount) as gmv from orders group by 1
),
带名字 as (
  select u.name, a.gmv from 聚合 a join users u on u.id = a.user_id
)
select * from 带名字 order by gmv desc limit 10;

易错 CTE 名别和真实表名撞（撞了会遮蔽原表，查错极难发现）；只能引用「写在它前面」的 CTE。

场景 老资料说「CTE 能防止条件推入、能提速」--按版本说话。

讲解 PG 11 及以前：CTE 一律物化（算一次存临时表），并且是优化栅栏--外层的 where 推不进去。PG 12 起：只被引用一次的 CTE 默认内联（像视图一样展开进主查询，栅栏没了）；被引用多次的仍然物化。

select version();   -- 先知道自己站在哪个版本上讨论
-- PG 12+：这个 CTE 会被内联，user_id 条件能推入
with t as (select * from orders where status = 2)
select * from t where user_id = '...';

易错 面试聊 CTE 必带版本号。不加版本说「CTE 是优化栅栏」，遇到懂 PG 的面试官直接扣分。

场景 一个昂贵 的 CTE 要被引用两次--到底算一遍还是两遍？

讲解 with t as materialized (...) 强制算一次存结果（引用 N 次也只算一次）；as not materialized 强制内联（每次引用展开一次）。默认规则记不住没关系--在意就显式写，语义摆在自己手里。

with t as materialized (   -- 昂贵计算只跑一次
  select user_id, count(*) as n from orders group by 1
)
select (select count(*) from t where n = 1) as 只下一单,
       (select count(*) from t where n >= 2) as 复购;

易错 物化 = 写临时结果（可能落盘），有真实代价；不是免费缓存。「引用多次的昂贵 CTE」才值得物化。

场景 网上文章标题《用 CTE 让查询快 10 倍》--你想知道它到底优化了什么。

讲解 CTE 解决的是可读性和拆解，不是速度。内联的 CTE 和一坨子查询在优化器眼里是同一个东西；物化倒是省了重复计算，但那是「你把逻辑组织对了」的副产品。快是因为索引、是因为少算了东西，从来不是因为 with 这个关键字。

易错 为了「性能」把查询套 10 层 CTE，既不快也不可读--方向就错了。

场景 运营原话：「近 3 个月，每月新客的 GMV 和老客的 GMV 分开算。」--先在注释里拆，再写 SQL。

讲解 ① 定粒度：一行 = 月份 × 新老客；② 找主表：事实在 orders；③ 补维度：「新客」要判定（首单是否落在当月）--先算每个用户的首单月份；④ 定过滤：近 3 个月在哪一步过滤；⑤ 聚合排序最后写。五步写在注释里，SQL 一段对应一步，写完的需求自己都能复查。

-- ① 粒度：月 × 新老客  ② 主表：orders
with 首单 as (                       -- ③ 维度：首单月份
  select user_id, min(created_at)::date as 首单日 from orders group by 1
)
select date_trunc('month', o.created_at) as 月份,
       case when date_trunc('month', f.首单日) = date_trunc('month', o.created_at)
            then '新客' else '老客' end as 客群,
       sum(o.total_amount) as gmv    -- ⑤ 最后聚合
from orders o
join 首单 f on f.user_id = o.user_id
where o.created_at >= date_trunc('month', current_date) - interval '3 months'  -- ④ 过滤
group by 1, 2
order by 1, 2;

易错 不拆就写的人，一半概率写到一半发现粒度错了全部推翻重来。五步法是慢就是快。

场景 「每个用户最近 3 笔订单」：子查询里得写「这个用户的」--普通子查询够不着外层。

讲解 join lateral (子查询)：右侧子查询里可以直接用左边表的列。执行直觉：对左表每一行，把它的值代进右查询跑一遍，结果并上来。它是「能返回一整组行的相关子查询」。

select u.name, t.*
from users u
join lateral (
  select id, total_amount, created_at
  from orders o
  where o.user_id = u.id            -- 引用了左边的 u.id
  order by created_at desc
  limit 3
) t on true
order by u.name, t.created_at desc;

易错 LATERAL 只能出现在 FROM / JOIN 的右侧；子查询里引用的别名必须在它左边出现过。

场景 有的用户没下过单--inner join lateral 会把他们整个吞掉。

讲解 lateral 子查询没有传统意义的连接条件，语法上用 on true 占位；改成 left join lateral (...) on true，右查询空结果也保留左行（右侧补 NULL）。「每个 X 及其最新 Y，没有也要」= 这句话。

select u.name, t.id, t.total_amount
from users u
left join lateral (
  select id, total_amount from orders o
  where o.user_id = u.id
  order by created_at desc limit 1
) t on true;

易错 少了 on true 是语法错；该用 left 的场景写成 inner，没下过单的用户静默消失。

场景 同一个需求三条路：整表 row_number、LATERAL、聚合后过滤。选哪条？

讲解 ① row_number() over (partition by 组) + 外层过滤：通用、组数无所谓；② LATERAL：每个组只扫自己要的那几行（前提：排序列上有索引），组多且每组取少量时常胜；③ 老式聚合拼接：别用了。D23 会正式展开 ①，今天先用 ② 的身体记住这个对比。

-- LATERAL 版：每个一级类目销量前 3 的商品
select c.name as 类目, t.name as 商品, t.销量
from categories c
join lateral (
  select p.name, sum(i.qty) as 销量
  from products p
  join order_items i on i.product_id = p.id
  where p.category_id = c.id
  group by p.id, p.name
  order by 销量 desc
  limit 3
) t on true
order by c.name, t.销量 desc;

易错 LATERAL 快的前提是「组内排序键有索引支撑」。没有索引时它退化成每组一次排序，可能更慢--所以 D20 练习要做耗时对比表。

场景 越看越像 D16 的相关子查询--本来就是一家。

讲解 本质都是「对外层每行执行一次的子查询」。区别在位置和产出：相关子查询放在 WHERE / SELECT 里，返回标量或布尔；LATERAL 放在 FROM 里，返回一组行、成为数据源。心法：要一个数，用相关子查询；要几行几列，用 LATERAL。

-- 要一个数（每用户订单数）
select u.name, (select count(*) from orders o where o.user_id = u.id) as n
from users u;
-- 要几行（每用户最近 3 单）-- 上面 LATERAL 的例子

易错 SELECT 列表里的子查询必须标量；想让它返回多行多列，硬写只会报错，换 LATERAL。