5月31日 23:58

SQLite CTE 怎么用？递归查询和复杂 SQL 怎么写？

SQLite 的 CTE，也就是公用表表达式，最直接的价值是把一段复杂查询拆成几个有名字的中间结果。它只在当前 SQL 语句里有效，不会真的创建一张持久表。SQLite 从 3.8.3 开始支持 CTE，后续版本还补充了更多优化能力。

如果一个 SQL 已经套了两三层子查询，或者你需要写递归查询，CTE 往往比继续堆括号更好读。但它不是性能灵药，有些场景会被优化器内联，有些场景可能被物化，真正上线前还是要看查询计划。尤其在移动端或嵌入式场景里，少一次无谓扫描就能少一次明显卡顿。

基础 CTE 怎么写？

最基本的写法是 WITH 名称 AS (...) SELECT ...。比如先算每个部门的平均工资，再筛出高于 60000 的部门：

sql
WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT department, avg_salary
FROM dept_avg
WHERE avg_salary > 60000
ORDER BY avg_salary DESC;

这段 SQL 用子查询也能写，但 CTE 的好处是中间结果有名字，读代码的人不用在嵌套括号里来回找。多个 CTE 可以连续定义，后面的 CTE 能引用前面的 CTE。

sql
WITH
  paid_orders AS (
    SELECT * FROM orders WHERE status = 'paid'
  ),
  user_total AS (
    SELECT user_id, SUM(amount) AS total
    FROM paid_orders
    GROUP BY user_id
  )
SELECT * FROM user_total WHERE total >= 1000;

递归 CTE 能解决什么问题？

递归 CTE 用 WITH RECURSIVE，通常由两部分组成：锚点查询负责给出起始行，递归查询负责一层层往下找。组织架构、分类树、菜单树、图路径、数字序列都可以用它。

sql
WITH RECURSIVE org(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
  WHERE org.depth < 10
)
SELECT * FROM org ORDER BY depth, id;

这里的 WHERE org.depth < 10 不是装饰，它是安全边界。真实数据里可能有环，比如 A 的上级是 B，B 的上级又被错误地改成 A。没有终止条件，递归查询会消耗大量资源。

CTE 和子查询怎么取舍？

如果查询只用一次、逻辑也短，子查询没问题。如果中间结果会被多处引用，或者你希望按步骤表达业务逻辑，CTE 更清楚。CTE 还适合和窗口函数配合，比如先汇总月销售额，再计算移动平均。

sql
WITH monthly_sales AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS total
  FROM orders
  GROUP BY month
)
SELECT
  month,
  total,
  AVG(total) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM monthly_sales;

如果数据量很大，并且中间结果会被反复使用，临时表有时比 CTE 更合适。临时表可以建索引，也能拆开执行和排查；代价是多了写入和生命周期管理。

写 CTE 时有哪些边界？

CTE 只在当前语句中可见，下一条 SQL 就不能用了。递归 CTE 必须有明确停止条件。SQLite 的递归查询默认也有深度限制，别把它当成无限图计算引擎。

还有一个常见误解：用了 CTE 就一定更快。事实上，CTE 更像可读性工具。是否更快取决于索引、过滤条件、SQLite 版本和优化器决策。遇到慢查询时，用 EXPLAIN QUERY PLAN 看它是否走了预期索引，比凭感觉改写更可靠。

sql
EXPLAIN QUERY PLAN
WITH duplicate_emails AS (
  SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
)
SELECT u.*
FROM users u
JOIN duplicate_emails d ON u.email = d.email;

追问

CTE 会不会真的创建临时表？

不一定。SQLite 可能把 CTE 内联成子查询，也可能选择物化成临时结果，取决于版本、引用次数和查询形态。不要把 CTE 理解成固定的临时表语法，它更像给优化器的一段命名查询。边界是性能敏感 SQL：如果你依赖某种执行方式，必须看 EXPLAIN QUERY PLAN，不能只看语法。

递归 CTE 为什么一定要写终止条件？

递归查询会一轮接一轮地产生新行，如果数据有环或条件写错，就可能跑很久。终止条件可以是深度限制、路径去重、目标节点命中，也可以组合使用。比如图遍历时常用 instr(path, next_id) = 0 避免回到旧节点。踩坑点是 ID 为数字时拼接字符串容易误判，生产场景最好加分隔符。

CTE 和视图有什么区别？

CTE 只在单条 SQL 中存在，适合一次性拆分复杂查询。视图是数据库对象，适合多个查询复用同一段逻辑。取舍上，如果逻辑和当前查询强绑定，用 CTE；如果多处都要用，并且希望统一维护，用视图。边界是视图会隐藏复杂度，排查性能问题时可能比显式 CTE 更绕。

大数据量下 CTE 慢怎么办？

先看查询计划，确认过滤条件和 JOIN 条件有没有走索引。再判断中间结果是否过大，是否需要把高选择性的过滤提前。若同一个中间结果被多次引用，可以考虑落到临时表并加索引。不要盲目把所有子查询改成 CTE，很多慢 SQL 的根因是缺索引或排序范围太大。

递归 CTE 能不能用来做图最短路径？

可以做简单路径探索，但不适合复杂的大规模图算法。SQLite 不是图数据库，递归 CTE 缺少专门的剪枝和优先队列机制，数据量上来后会很吃力。小型权限树、菜单树、分类树没问题；社交关系、路线规划这类场景要谨慎。真正需要最短路径时，更建议在应用层或专门的图计算工具里处理。

标签：Sqlite