DAY 18 W3 · 需求变绕了：子查询、CTE 与章法高频

类目树整棵下钻：递归 CTE

老板在类目管理页点了「电子」，想看它下面所有层级类目的汇总销售。层级不固定，普通 JOIN 搞不定。

学 45 min

练 60 min

盘 15 min

共 120 分钟

完成这一天

学 · 45 min

01WITH RECURSIVE 的三段结构：初始项 + UNION ALL + 递归项
场景「电子」下面所有层级--几层不知道，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 会去重，语法没错但轮次语义悄悄变了。
02递归的终止条件与执行过程
场景递归没有 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）时每轮都有新行，递归永远不停--下一条讲怎么防。
03防死循环：记录已访问路径
场景脏数据造出环形类目树，一条递归查询把 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) 停掉它。
04累积深度与路径拼接的技巧
场景「每个分类的完整路径（电子 > 手机 > 配件）」和「每个分类在第几层」--两个需求一个套路。
递归项里维护「随身列」：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。

练 · 60 min

查每个分类的完整路径（如「电子 > 手机 > 配件」）

参考答案

随身列模式：路径 = 父.路径 || ' > ' || 本名。seed 里类目名是「分类N / 子分类N」，所以路径长成「分类3 > 子分类17」；名字里的 ::text 是为了钉死路径列的类型。

with recursive tree as (
  select id, name, parent_id, name::text as 路径, 1 as 层级
  from categories
  where parent_id is null                       -- 初始项：从全部顶级开始
  union all
  select c.id, c.name, c.parent_id,
         t.路径 || ' > ' || c.name, t.层级 + 1  -- 随身列：路径和深度都从父行继承
  from categories c
  join tree t on c.parent_id = t.id
)
select name as 类目, 路径, 层级
from tree
order by 路径;

给定「电子」，查它的全部子孙分类及其销售汇总

参考答案

seed 里没有叫「电子」的类目，把「分类1」当成它跑。递归负责「所有层级」，不假设树只有两层；聚合在外面另起一段，别把汇总逻辑塞进递归项。

with recursive sub as (
  select id, name from categories
  where parent_id = (select id from categories where name = '分类1')   -- 初始项：直接孩子
  union all
  select c.id, c.name
  from categories c
  join sub s on c.parent_id = s.id                                     -- 递归项：再往下走一层
)
select s.name as 类目, sum(i.qty * i.unit_price) as gmv
from sub s
join products p on p.category_id = s.id
join order_items i on i.product_id = p.id
group by 1
order by gmv desc;

用递归 CTE 生成 2026 全年日期序列（不用 generate_series）

参考答案

递归没有 while，终止是隐式的：某轮递归项产出 0 行就停。改 where 的上界就能生成任意区间--generate_series 的手写版，递归 CTE 最经典的入门题。

with recursive days as (
  select date '2026-01-01' as 日期      -- 初始项：起点
  union all
  select 日期 + 1                        -- 递归项：往前走一天
  from days
  where 日期 < date '2026-12-31'         -- 终止条件：走到年底不再产生新行
)
select 日期 from days order by 1;        -- 365 行

算出每个分类所在的层级深度

参考答案

深度是随身列：初始项给顶级定 1，递归项每层 +1。这题结果验证了「商品的 category_id 全挂在二级」的事实。

with recursive tree as (
  select id, name, 1 as 层级
  from categories
  where parent_id is null
  union all
  select c.id, c.name, t.层级 + 1
  from categories c
  join tree t on c.parent_id = t.id
)
select 层级, count(*) as 类目数
from tree
group by 1
order by 1;
-- 1 级 30 个、2 级 45 个；想看每个类目自己的层级，把聚合拆掉直接 select 即可

自建 employees 表做组织架构下钻，输出每人的汇报链

参考答案

和类目树是同一个模板：邻接表（boss_id 指回本表）+ 递归下钻 + 路径随身列。练完 drop table employees 收尾，别留在库里污染后面的题。

drop table if exists employees;
create table employees (
  id      int primary key,
  name    text,
  boss_id int                    -- 顶级（老板）为 NULL
);
insert into employees values
  (1, '老板', null),
  (2, '技术总监', 1),
  (3, '后端组长', 2),
  (4, '你（DBA）', 3),
  (5, '运营总监', 1),
  (6, '运营专员', 5);

with recursive tree as (
  select id, name, boss_id, name::text as 汇报链
  from employees
  where boss_id is null
  union all
  select e.id, e.name, e.boss_id,
         t.汇报链 || ' > ' || e.name
  from employees e
  join tree t on e.boss_id = t.id
)
select name as 员工, 汇报链
from tree
order by 汇报链;
-- 「你（DBA）」那行：老板 > 技术总监 > 后端组长 > 你（DBA）

过关标准 不看资料写出一个递归 CTE，并逐段解释初始项和递归项各做什么。

学 · 45 min

01WITH RECURSIVE 的三段结构：初始项 + UNION ALL + 递归项

02递归的终止条件与执行过程

03防死循环：记录已访问路径

04累积深度与路径拼接的技巧

练 · 60 min

出题端点设置