通用表表达式（Common Table Expression）能够简化复杂的子查询和连接查询，实现树状结构数据的遍历，提高 SQL 语句的可读性和性能。

表即变量

sql
WITH cte_name(col1, col2, ...) AS (
	subquery
)
SELECT * FROM cte_name;

• cte_name: CTE 的名称，后面是可选的字段名。
• AS 后的 subquery 用于定义 CTE。
• SELECT 是主查询，可以引用所定义的 CTE。还可以使用 INSERT、UPDATE、DELETE 等。

sql
WITH t(n) AS (
	SELECT 1 -- oracle 需要使用 SELECT 1 FROM dual
)
SELECT n
FROM t;

WITH 语句定义了一个变量，这个变量的值是一个表，所以称为通用表表达式。CTE 和临时表或子查询类似，可以用于 SELECT、INSERT、UPDATE 以及 DELETE 等语句。

sql
-- 定义多个 CTE
WITH t1(n) AS (
	SELECT 1 -- oracle 需要使用 SELECT 1 FROM dual
),
t2(m) AS (
	SELECT n+1
	FROM t1 -- 可以引用前面的 CTE
)
SELECT t1.n, t2.m
FROM t1
CROSS JOIN t2;

强大的递归

sql
WITH RECURSIVE cte_name AS (
	cte_query_initial -- 初始化部分
	UNION [ALL] -- 合并
	cte_query_iterative -- 递归部分，可以对当前 CTE 进行自我引用
) SELECT * FROM cte_name;

• 每一次递归查询语句执行的结果都会再次作为输入，传递给下一次查询。
• 如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归。
• oracle 和 mss 不支持 RECURSIVE，而直接使用 WITH 定义递归形式的CTE，同时它们必须使用 UNION ALL 运算符。
• sqlite 可以省略 RECURSIVE。

一些使用示例。

sql
-- 递归生成1～10
-- mysql pg sqlite
WITH RECURSIVE t(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

-- oracle
WITH t(n) AS
(
	SELECT 1 FROM dual
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

-- mss
WITH t(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

1. 首先运行初始化语句，生成数字1。
2. 第1次运行递归部分，此时n等于1，生成数字2（n+1）​。
3. 第2次运行递归部分，此时n等于2，生成数字3。
4. 继续运行递归部分，直到n等于9，生成数字10。
5. 第10次运行递归部分，此时n等于10。由于不满足查询条件（WHERE n < 10）​，不返回任何结果，同时终止递归。
6. 最后，主查询语句返回t中的全部数据，也就是一个1～10的数字序列。

sql
-- 遍历层次/树结构，自顶向下
-- mysql
WITH RECURSIVE employee_path (emp_id, emp_name, path) AS
(
	-- 初始部分
	SELECT emp_id, emp_name, emp_name AS path
	FROM employee
	WHERE manager IS NULL -- 老板，是树的根
	UNION ALL
	-- 递归部分
	SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)
	FROM employee_path ep
	JOIN employee e ON ep.emp_id = e.manager -- 当前层关联下一层
)
SELECT emp_name AS "员工姓名", path AS "管理路径"
FROM employee_path
ORDER BY emp_id;

• mysql 递归次数限制由 cte_max_recursion_depth 控制。
• pg sqlite 没有递归次数限制。
• oracle 能够检测出递归死循环。
• mss 默认递归100次，可以在查询中使用 MAXRECURSION 选项设置。
• 递归终止的 WHERE 条件要在 CTE 的定义中，不能通过主查询实现。

参考

• 理解 MySQL with recursive 的执行过程