DAY 21 W3 · 需求变绕了：子查询、CTE 与章法 测评

周测：白板手写模拟

三周了。本周测评改成白板模式--纸上手写、不许运行，模拟面试现场。

学 10 min
练 70 min
盘 40 min
共 120 分钟

复盘 · 10 min

  1. 01整理本周的 CTE / 递归 / LATERAL 模板到 notes.md

测评 · 110 min

  1. 60 分钟纸上手写 6 题（不许运行、不许查文档）--模拟白板环节
    参考答案

    白板规则：纸笔、不运行、不查文档，60 分钟 6 题平均每题 10 分钟。出题范围就是本周五种武器：① 子查询三个位置（标量 / 派生表 / 条件）；② NOT IN 遇 NULL 的推导 + NOT EXISTS 写法；③ 递归 CTE（路径或日期序列）；④ 多级 CTE 拆一个绕需求；⑤ LATERAL 的 Top-N per group。手写时最容易漏的是 with recursive 的 union all 和 lateral 的 on true--这两处白板上丢了整条就跑不通。

  2. 上机逐题验证，统计有几题一次通过
    参考答案

    上机验证时别边跑边改：整题跑完再统一对答案，才能统计出真实的「一次通过」数。一次通过 = 首次运行即返回正确结果；语法小错（漏逗号、别名拼错）不算通过，白板环节它们同样是错。

  3. 把写错的地方标红，归入 mistakes.md
    参考答案

    归因沿用第一周的三分法：没懂概念（回当天「学」栏重看并口述）、记不住语法（连续 3 天默写模板）、看错题意（读题先圈名词和限定词）。本周新增一类高频错因：模板记住了但「初始项 / 锚点选错」--递归和 LATERAL 都是从锚点长出来的，锚点错全盘错。

  4. 对着镜子讲一遍其中最难那题的解题思路
    参考答案

    讲题结构：先复述需求 -> 说拆解思路（为什么分这几步）-> 逐段讲 SQL（这段在干嘛、为什么放这里）-> 收尾讲边界（NULL、空集、层级不定）。60 秒内讲完，卡壳的那一句就是还没真懂的地方，回炉。

过关标准 6 题中至少 4 题手写版本能直接跑通。

今日复盘

  1. D+1 默写 D20 每个类目销量前三：LATERAL
    用 LATERAL 查每个用户最近 3 笔订单
    参考答案

    LATERAL 让右侧子查询引用左侧的列：对每个用户把 u.id 代进右查询跑一遍。想保留没下过单的用户，把 join lateral 换成 left join lateral ... on true，右侧补 NULL。

    select u.name, t.id as 订单id, t.total_amount, t.created_at
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;
  2. D+3 重做 D18 类目树整棵下钻：递归 CTE
    查每个分类的完整路径（如「电子 > 手机 > 配件」）
    参考答案

    随身列模式：路径 = 父.路径 || ' > ' || 本名。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 路径;
  3. D+7 口头解释 D14 周测：JOIN 陷阱专项
    回看本周错题，重点看 JOIN 类