SQLite 递归 CTE：用 WITH RECURSIVE 查询树和生成序列

SQL 里的递归听起来很怪，看过例子就明白了

大多数查询都是从已有的数据中返回行。递归 CTE 不一样：它把自己的输出作为输入再送回去，一步一步地生成行，直到没有新内容可加为止。遍历一棵深度未知的树，或者在没有数字表的情况下生成 1 到 100 的数字，靠的都是它。

它的结构永远是这样：

WITH RECURSIVE name(columns) AS (
    -- anchor: the starting rows
    SELECT ...
    UNION ALL
    -- recursive: rows derived from the previous step
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

上面是锚点查询，中间是 UNION ALL，下面是递归查询。SQLite 先执行一次锚点查询，然后不断执行递归部分，每次都使用上一轮产生的行，直到它不再返回新行。然后就停下来。

从 1 数到 10

最简单的递归 CTE 用来生成序列，连表都不需要：

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

一步步看它是怎么执行的：

1. 锚点查询产生一行：n = 1。
2. 递归步骤拿到这一行，计算 n + 1 = 2，2 < 10 为真，所以保留这一行。
3. 下一轮拿到 n = 2，产生 n = 3，依此类推。
4. 当 n 到达 10 时，10 < 10 为假，递归步骤不再返回任何行，SQLite 停止。

WHERE n < 10 就是终止条件。没有它，查询会永远运行下去。

生成日期序列

思路相同，在实际报表中很有用：把某个范围内的每一天都补齐，哪怕那天什么都没发生：

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

通常你会把它和事件表做 LEFT JOIN，才能正确统计没有事件的日子。普通的 GROUP BY date 会直接跳过空的日子；而日期序列无论如何都会给每一天一行。

遍历父子树

这是最经典的用法。下面是一张员工表，每一行都指向自己的经理：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

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
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

锚点查询选出根节点（没有经理的那个人）。递归步骤把员工表和 CTE 连接起来，找出所有 manager_id 与 CTE 中已有 id 匹配的人。每一轮都往下深入一层。depth 只是我们加上的计数器，用来缩进输出。

这对任意深度的树都有效。两层也好，十层也好，查询都不用改。

查找某一行的所有祖先

换个方向。不是从根往下走，而是从某个员工往上走，找出他的整条上级链：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

锚点是起始员工。每一步递归都跳到上一级。到达根节点时 SQLite 就会停止：根节点的 manager_id IS NULL，连接找不到任何行。

这个模式适用于面包屑导航、楼层式评论、分类路径，以及任何需要“一路往上走到顶”的场景。

终止条件与无限循环

最常见的 bug 是忘了写终止条件，或者写的条件永远不会生效。比较一下：

-- Runs forever:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

这里没有任何 WHERE 子句能让它返回零行。SQLite 会乐此不疲地一直数下去。

两个防御性习惯：

1. 递归部分一定要有限制增长的 WHERE 子句。
2. 开发时给外层 SELECT 加上 LIMIT 作为保险：就算你的终止条件写错了，查询也会结束。

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;

CTE 本身没有上限，但 LIMIT 5 让外层查询提前结束。SQLite 足够聪明，不会递归到超出 LIMIT 所需的范围。这在探索数据时很有用，但不能代替生产代码中真正的终止条件。

图中的环

树不会有环，一般的图却可能有。如果数据中有环，简单的递归 CTE 会永远循环下去。解决方法是记录已经走过的路径，拒绝重复访问节点：

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path 是一个用逗号分隔的字符串，记录已经访问过的节点。在加入新节点之前，WHERE 子句会检查它是否已经在里面。没有这个保护，1 → 2 → 3 → 1 这个环就会无限循环。

SQL 没有内置的“已访问集合”，你需要自己构建一个，通常用字符串，或者和目前为止的 CTE 做连接。

递归 CTE 与自连接

如果只需要一两层，自连接更简单，也更快：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

这能回答“每个人的直属经理是谁”。但如果你需要“所有向上汇报到 Ada 的人，不管隔了多少层”，深度未知，那就只有递归 CTE 能干净地处理。根据需要的深度选择工具：

• 深度固定且很浅：用自连接，也许两三个。
• 深度未知或任意：用 WITH RECURSIVE。

心智模型

递归 CTE 就是一个用声明式写法表达的循环：

• 锚点查询是循环的初始值。
• 递归查询是循环体：它根据当前这批行产生下一批行。
• 终止条件是循环的退出判断：返回零行时，循环结束。
• UNION ALL 把所有结果累积到最终的结果集中。

一旦脑子里建立了这个对应关系，语法就不再显得奇怪了。你其实是在用 SQL 写一个 for 循环。

下一步：索引

递归 CTE 会遍历很多行，而递归步骤中的连接每一轮都要执行。如果连接列没有索引，性能会迅速断崖式下跌。下一章讲索引，而 manager_id 正是能从索引中受益的那种列。