通用表表达式（WITH 语句）能够将复杂的查询语句模块化，实现结果集的重复使用，提高 SQL 语句的可读性和性能。同时，递归形式的 CTE 提供了遍历层次数据和分析网络图数据的强大功能。
本文比较了五种主流数据库中的递归查询功能，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
| 功能 | MySQL | Oracle | SQL Server | PostgreSQL | SQLite |
|---|---|---|---|---|---|
| 简单CTE | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
| 递归CTE | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
表即变量
在使用编程语言时，我们通常会定义一些变量和函数（方法）。变量可以被重复使用；函数（方法）可以将代码模块化，从而提高程序的可读性与可维护性。
与此类似，SQL 中的通用表表达式也能够实现查询结果的重复利用，简化复杂的连接查询和子查询。SQL 通用表表达式的基本语法如下：
WITH cte_name(col1, col2, ...) AS (
subquery
)
SELECT * FROM cte_name;
其中，WITH 关键字表示定义通用表表达式（简称 CTE），因此通用表表达式也被称为WITH 查询。cte_name 指定了 CTE 的名称，后面是可选的字段名。AS 关键字后面的子查询是CTE 的定义语句，定义了它的表结构和数据。最后的 SELECT 是主查询语句，它可以引用前面
定义的 CTE。除了 SELECT 之外，主查询语句也可以是 INSERT、UPDAT 或 DELETE 等。
例如，以下是一个简单的 CTE 示例：
WITH t(n) AS (
SELECT 1
)
SELECT n
FROM t;
其中，WITH 关键字表示定义 CTE。t 就是一个 CTE，包含一个字段 n 并且只有一行数据。最后，我们在主查询语句中使用了前面定义的 t。查询返回的结果如下：
n
-
1
提示：WITH 语句定义了一个变量，这个变量的值是一个表，所以称为通用表表达式。CTE 和临时表或者子查询类似，可以用于 SELECT、INSERT、UPDATE 以及 DELETE 等语句。在 Oracle 数据库中 WITH 语句被称为子查询因子。
CTE 与子查询类似，只在当前语句中有效。我们在一个 WITH 子句中可以定义多个 CTE，并且已经定义的 CTE 可以被后续的 CTE 引用。例如，以下示例中定义了 2 个 CTE：
WITH t1(n) AS (
SELECT 1
),
t2(m) AS (
SELECT n+1
FROM t1
)
SELECT t1.n, t2.m
FROM t1
CROSS JOIN t2;
其中，WITH 关键字表示定义 CTE。t1 包含一个字段 n 并且只有一行数据。t2 包含一个字段 m，同时在 t2 的定义中引用了前面的 t1。两个 CTE 之间使用逗号进行分隔。最后，我们在主查询语句中通过 t1 和 t2 的交叉连接返回了两个表中的数据。查询返回的结果如下：
n|m
-|-
1|2
我们再来看一个示例，以下语句中使用了子查询：
SELECT dept_cost.dept_name AS "部门名称",
dept_cost.total AS "部门成本",
detp_avg_cost.avg_total AS "平均成本"
FROM (SELECT dept_name, SUM(salary*12 + COALESCE(bonus, 0)) total
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY dept_name
) dept_cost
JOIN (SELECT SUM(total)/COUNT(*) avg_total
FROM (SELECT dept_name, SUM(salary*12 + COALESCE(bonus, 0)) total
FROM employee e
JOIN department d
ON (e.dept_id = d.dept_id)
GROUP BY dept_name
) dept_cost
) detp_avg_cost
ON (dept_cost.total > detp_avg_cost.avg_total);
其中，FROM 子句中的子查询 dept_cost 返回了每个部门的总体成本。我们在 JOIN 子句中再次定义了相同的子查询 dept_cost，并且基于该子查询定义了子查询 detp_avg_cost，得到了所有部门的平均成本。最后，我们在外部查询中返回了部门成本大于平均成本的部门。查询返回的结果如下：
部门名称 |部门成本 |平均成本
--------|---------|-------------
行政管理部|990000.00|601420.000000
研发部 |824400.00|601420.000000
通过以上示例可以看出，当逻辑稍微复杂一些时，使用子查询编写的语句不易阅读和理解，同时也为性能优化增加了难度。
我们使用 CTE 将上面的示例进行改写：
WITH dept_cost(dept_name, total) AS
(SELECT dept_name, SUM(salary*12 + COALESCE(bonus, 0))
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY dept_name
),
detp_avg_cost(avg_total) AS (
SELECT SUM(total)/COUNT(*) avg_total
FROM dept_cost
)
SELECT dept_cost.dept_name AS "部门名称",
dept_cost.total AS "总成本",
detp_avg_cost.avg_total AS "平均成本"
FROM dept_cost
JOIN detp_avg_cost
ON (dept_cost.total > detp_avg_cost.avg_total);
其中，dept_cost 是一个 CTE，包含了每个部门的名称和成本。detp_avg_cost 也是一个 CTE，引用了前面定义的 dept_cost，包含了所有部门的平均成本。最后，我们在主查询语句，通过这两个 CTE 的连接查询返回了部门成本大于平均成本的部门。显然，使用 CTE 编写的查询语句更易理解，且更加高效。
提示：通用表表达式（WITH 语句）可以将 SQL 语句进行模块化和重复利用，从而提高复杂查询语句的可读性和性能。
强大的递归
除提高查询的可读性和性能外，CTE 还支持在定义中进行自我引用，也就是实现了编程语言中的递归调用。递归形式的通用表表达式可以用于遍历具有层次结构或者树状结构的数据，例如遍历组织结构、查询地铁线路图。
递归查询语法
递归 CTE 的基本语法如下：
WITH RECURSIVE cte_name AS(
cte_query_initial -- 初始化部分
UNION [ALL]
cte_query_iterative -- 递归部分
) SELECT * FROM cte_name;
其中，关键字 WITH RECURSIVE 表示定义递归形式的 CTE（即递归 CTE）。递归 CTE 的定义包含两部分， cte_query_initial 是初始化查询语句，用于创建初始结果集 。cte_query_iterative 是递归查询语句，可以对当前 CTE 进行自我引用。每一次递归查询语句执行的结果都会再次作为输入，传递给下一次查询。如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归。最后，UNION [ALL] 运算符用于合并这两个结果集。
提示：Oracle 和 Microsoft SQL Server 不支持 RECURSIVE，而直接使用 WITH 定义递归形式的 CTE，同时它们必须使用 UNION ALL 运算符。SQLite 可以省略 RECURSIVE。
接下来我们介绍一些递归 CTE 的使用示例。
生成数字序列
以下查询通过递归 CTE 生成一个 1～10 的数字序列：
-- MySQL、PostgreSQL 以及 SQLite
WITH RECURSIVE t(n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;
-- Oracle、Microsoft SQL Server
WITH t(n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;
其中，WITH 或者 WITH RECURSIVE 表示定义递归形式的 CTE。该语句的执行过程如下：
- 首先运行初始化语句，生成数字 1。
- 第 1 次运行递归部分，此时 n 等于 1，生成数字 2（n+1）。
- 第 2 次运行递归部分，此时 n 等于 2，生成数字 3。
- 继续运行递归部分，直到 n 等于 9，生成数字 10。
- 第 10 次运行递归部分，此时 n 等于 10。由于不满足查询条件（WHERE n < 10），不返回任何结果，同时终止递归。
- 最后，主查询语句返回 t 中的全部数据，也就是一个 1～10 的数字序列。
查询返回的结果如下：
n
--
1
2
3
...
9
10
从该示例中可以看出，递归 CTE 非常合适用来生成具有某种规律的数字序列，例如斐波那
契数列（Fibonacci Series）等。
遍历层次结构
员工表（employee）中存储了员工的各种信息，包括员工编号、姓名以及员工经理的编号。其中“刘备”没有上级，他的经理字段 manager 为空。
以下查询利用递归 CTE 生成一个组织结构图，显示每个员工从上到下的管理路径：
-- 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;
其中，employee_path 是一个递归 CTE。UNION ALL 前面的初始化部分用于查找上级经理为空的员工，也就是“刘备”。path 字段用于保存从上到下的管理路径。然后第一次执行递归部分，将初始化的结果集（employee_path）与员工表进行连接查询，并找出“刘备”的所有直接下属。CONCAT 函数用于将之前的管理路径加上当前员工的姓名，生成新的管理路径。第一次执行递归部分之后的结果如下：
员工姓名|管理路径
-------|----------
刘备 |刘备
关羽 |刘备->关羽
张飞 |刘备->张飞
诸葛亮 |刘备->诸葛亮
孙尚香 |刘备->孙尚香
赵云 |刘备->赵云
查询继续执行递归部分，不断返回其他员工的下级员工，直到不再返回新的员工为止。查询最终返回的查询结果如下：
员工姓名|管理路径
-------|-------------------
刘备 |刘备
关羽 |刘备->关羽
张飞 |刘备->张飞
...
简雍 |刘备->关羽->法正->简雍
孙乾 |刘备->关羽->法正->孙乾
如果使用 Oracle 数据库，我们需要删除 RECURSIVE 关键字，并且将 CONCAT 函数替换为连接运算符（||）。
如果使用 Microsoft SQL Server，我们需要将查询语句修改如下：
-- Microsoft SQL Server
WITH employee_path (emp_id, emp_name, path) AS
(
SELECT emp_id, emp_name, CAST(emp_name AS VARCHAR) AS path
FROM employee
WHERE manager IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name,
CAST(CONCAT(ep.path, '->', e.emp_name) AS VARCHAR)
FROM employee_path ep
JOIN employee e ON ep.emp_id = e.manager
)
SELECT emp_name AS "员工姓名", PATH AS "管理路径"
FROM employee_path;
首先，我们删除了 RECURSIVE 关键字。其次，我们使用 CAST 函数将 path 字段的类型转换为 VARCHAR 类型，否则查询会返回数据类型不匹配的错误。
如果使用 PostgreSQL，我们需要使用 CAST 函数将 path 字段的类型转换为 VARCHAR(n) 或者 TEXT 类型，否则查询会返回数据类型不匹配的错误。
如果使用 SQLite，我们需要将 CONCAT 函数替换为连接运算符（||）。
递归的终止
一般而言，递归 CTE 的定义中需要包含一个终止递归的条件。否则的话，递归将会进入死循环。例如，以下语句删除了生成数字序列示例中的 WHERE 条件：
-- MySQL、PostgreSQL 以及 SQLite
WITH RECURSIVE t(n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM t
)
SELECT n FROM t;
-- Oracle、Microsoft SQL Server
WITH t(n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM t
)
SELECT n FROM t;
如果我们执行以上语句，MySQL 默认递归 1000 次（由系统变量 cte_max_recursion_depth控制）后终止递归，并提示错误。PostgreSQL 和 SQLite 没有进行递归次数限制，查询进入死循环。Oracle 能够检测到查询语句中的死循环问题，并提示错误。Microsoft SQL Server 默认递归100 次（可以在查询中使用 MAXRECURSION 选项进行设置）后终止，并提示错误。
提示：递归终止条件可以是遍历完表中的所有数据后不再返回更多结果，或者在递归查询部分的 WHERE 子句中指定一个终止条件。
另外，限制递归次数的终止条件必须写在 CTE 的定义中，而不能通过主查询实现，例如：
-- MySQL、PostgreSQL 以及 SQLite
WITH RECURSIVE t(n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM t
)
SELECT n FROM t WHERE n < 10;
由于主查询语句中的 WHERE 条件并不会对 CTE 产生影响，因此以上语句仍然会返回错误或者进入死循环。
