使用 CTEs （公用表表达式）¶
- 另请参阅：
本主题内容：
什么是 CTE？¶
CTE （公用表表达式）是在 WITH 子句中定义的命名子查询。您可以将 CTE 视为一个临时 视图，用于定义 CTE 的语句。定义 CTE 临时视图的名称、列名的可选列表和查询表达式（即 SELECT 语句）。查询表达式的结果实际上是一个表。该表的每一列都对应于列名（可选）列表中的一列。
以下代码是使用 CTE 的查询示例：
WITH
my_cte (cte_col_1, cte_col_2) AS (
SELECT col_1, col_2
FROM ...
)
SELECT ... FROM my_cte;
在上面的示例中，CTE 从包含 my_cte (cte_col_1, cte_col_2) AS ( 的行开始，到包含 ) 的行结束。
避免选择与以下内容一致的 CTE 名称：
表、视图或物化视图。如果查询定义了具有特定名称的 CTE，则 CTE 优先于表等。
CTE 可以是递归的，也可以是非递归的。递归 CTE 是引用自身的 CTE。递归 CTE 可以根据需要多次将表联接到自身，以处理表中的分层数据。
CTEs 提高模块化程度并简化维护。
递归 CTEs 和分层数据¶
递归 CTEs 使您能够处理分层数据，例如部件分解（组件、子组件）或管理层次结构（经理、员工）。有关分层数据以及查询分层数据的其他方法的详细信息，请参阅 查询分层数据。
递归 CTE 允许您联接层次结构的所有级别，而无需事先知道有多少个级别。
递归 CTE 语法概述¶
本部分概述了语法以及语法与递归工作方式的关系：
WITH [ RECURSIVE ] <cte_name> AS
(
<anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;
- 其中：
anchor_clause选择表示层次结构顶部的初始行或一组行。例如，如果您尝试显示公司中的所有员工，则锚点子句将选择公司的总裁。
锚点子句是一个 SELECT 语句，可以包含任何受支持的 SQL 构造。锚点子句不能引用
cte_name。recursive_clause根据上一层选择层次结构的下一层。在第 1 次迭代中，上一层是锚点子句的结果集。在后续迭代中，上一层是最近完成的迭代。
recursive_clause是一个 SELECT 语句；但是，该语句仅限于投影、联接和筛选。此外，语句中 不 允许出现以下内容：汇总函数或窗口函数。
GROUP BY、ORDER BY、LIMIT或DISTINCT。
递归子句可以像
cte_name一样引用常规表或视图。
有关语法的更详细说明，请参阅 WITH。
从逻辑上讲，递归 CTE 的计算方式如下：
对
anchor_clause进行评估，并将其结果写入最终结果集和工作表。cte_name实际上是工作表的别名；换句话说，引用cte_name的查询从工作表中读取。当工作表不为空时：
使用引用
cte_name的工作表的当前内容计算recursive_clause。recursive_clause的结果将同时写入最终结果集和临时表。工作表被临时表的内容覆盖。
实际上，上一次迭代的输出存储在一个名为 cte_name 的工作表中，然后该表就是下一次迭代的输入之一。工作表仅包含最近一次迭代的结果。到目前为止，所有迭代的累积结果都存储在其他位置。
在最后一次迭代之后，通过引用 cte_name，累积结果可用于主 SELECT 语句。
递归 CTE 注意事项¶
无限循环的可能性¶
错误地构造递归 CTE 会导致无限循环。在这些情况下，查询将继续运行，直到查询成功、查询超时（例如，超过 STATEMENT_TIMEOUT_IN_SECONDS 参数指定的秒数）或 取消查询。
有关无限循环如何发生以及如何避免此问题的指南的信息，请参阅 对递归 CTE 进行故障排除。
非连续层次结构¶
本主题描述了层次结构以及递归 CTEs 如何使用父子关系。在本主题中的所有示例中，层次结构都是连续的。
有关非连续层次结构的信息，请参阅 查询分层数据。
示例¶
本部分包括非递归和递归 CTEs 示例，用以对比这两种类型。
非递归、两级、自联接 CTE¶
此示例使用员工和经理的表：
CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);INSERT INTO employees (title, employee_ID, manager_ID) VALUES ('President', 1, NULL), -- The President has no manager. ('Vice President Engineering', 10, 1), ('Programmer', 100, 10), ('QA Engineer', 101, 10), ('Vice President HR', 20, 1), ('Health Insurance Analyst', 200, 20);
此员工表的两级自联接如下所示：
SELECT emps.title, emps.employee_ID, mgrs.employee_ID AS MANAGER_ID, mgrs.title AS "MANAGER TITLE" FROM employees AS emps LEFT OUTER JOIN employees AS mgrs ON emps.manager_ID = mgrs.employee_ID ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID; +----------------------------+-------------+------------+----------------------------+ | TITLE | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE | |----------------------------+-------------+------------+----------------------------| | President | 1 | NULL | NULL | | Vice President Engineering | 10 | 1 | President | | Vice President HR | 20 | 1 | President | | Programmer | 100 | 10 | Vice President Engineering | | QA Engineer | 101 | 10 | Vice President Engineering | | Health Insurance Analyst | 200 | 20 | Vice President HR | +----------------------------+-------------+------------+----------------------------+
上面的查询显示了所有员工。在报告中，每个经理的员工都显示在其经理附近。但是，报告不会直观地显示层次结构。如果不仔细查看数据，您就不知道组织中有多少个级别，并且需要读取每一行才能查看哪些员工与特定经理相关联。
递归 CTE 可以将此分层数据显示为横向树，如下一部分所示。
具有缩进输出的递归 CTE¶
以下是使用递归 CTE 的两个示例：
第一个使用缩进来显示层次结构的不同级别。为了简化此示例，代码不会按特定顺序生成行。
第二个示例使用缩进，并在经理的正下方显示每个经理的员工。
无序输出¶
这是第一个示例。
1) WITH RECURSIVE managers
2) (indent, employee_ID, manager_ID, employee_title)
3) AS
4) (
5)
6) SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
7) FROM employees
8) WHERE title = 'President'
9)
10) UNION ALL
11)
12) SELECT indent || '--- ',
13) employees.employee_ID, employees.manager_ID, employees.title
14) FROM employees JOIN managers
15) ON employees.manager_ID = managers.employee_ID
16) )
17)
18) SELECT indent || employee_title AS Title, employee_ID, manager_ID
19) FROM managers
20) ;
查询包括以下部分：
第 2 行包含“视图”(CTE) 的列名。
第 4-16 行包含 CTE。
第 6-8 行包含 CTE 的锚点子句。
第 12-15 行包含 CTE 的递归子句。
第 18-19 行包含使用 CTE 作为视图的主 SELECT。此 SELECT 引用以下内容：
在第 1 行中定义的 CTE 名称 (
managers)。在第 2 行中定义的 CTE 的列（
indent、employee_id等）。
CTE 包含两个 SELECT 语句：
锚点子句中的 SELECT 语句执行一次，并提供层次结构的第一（顶层）级中的一组行。
递归子句中的 SELECT 可以引用 CTE。可以将查询视为迭代，每次迭代都基于前一次迭代的查询结果。
在经理/员工示例中，锚点子句发出第一行，即描述公司总裁的行。
在递归子句的下一次迭代中，递归子句查找经理是公司总裁的所有行（即，查找所有副总裁）。第 3 次迭代查找经理是副总裁之一的所有员工。迭代将继续进行，直到出现一个迭代检索到的所有行都是不管理任何人的叶级员工的行。该语句又进行了一次迭代，查找（但没有找到）经理是叶级员工的任意员工。该迭代生成 0 行，并且迭代停止。
在这些迭代中，UNION ALL 子句会累积结果。每次迭代的结果都会添加到先前迭代的结果中。在最后一次迭代完成后，累积的行（如 WITH 子句中生成的任何行）将可用于查询中的主 SELECT 子句。然后，该主 SELECT 可以查询这些行。
此特定示例查询使用缩进来显示数据的层次结构性质。查看输出会发现，员工级别越低，员工的数据缩进地越远。
缩进由名为 indent 的列控制。缩进从 0 个字符（锚点子句中的空字符串）开始，每次迭代（即层次结构中的每个级别）增加 4 个字符 (---)。
毫不奇怪，正确构造联接并在递归子句中选择正确的列非常重要。递归子句的 SELECT 中的列必须与锚点子句中的列正确对应。请记住，查询从总裁开始，然后选择副总裁，然后选择直属于总裁的人，等等。每次迭代都会查找 manager_id 字段对应于上一次迭代中生成的 managers.employee_id 值之一的员工。
换一种说法，经理“视图”中的员工 ID 是下一级员工的经理 ID。在每次迭代期间，员工 IDs 必须在层次结构（总裁、副总裁、高级经理、初级经理等）中向下推进。如果员工 IDs 没有推进，那么查询可以无限循环（如果相同的 manager_ID 在不同的迭代中不断出现在 managers.employee_ID 列中），或者跳过一个级别，或者以其他方式失败。
有序输出¶
前面的示例没有 ORDER BY 子句，所以即使每个员工的记录都正确缩进，每个员工也不一定直接出现在他们的经理下面。下面的示例生成具有正确缩进的输出，并且每个经理的员工直接位于其经理的下方。
查询的 ORDER BY 子句使用一个名为 sort_key 的附加列。排序键随着递归子句的迭代而累积；您可以将排序键视为包含您上方的整个命令链（您的经理、经理的经理等）的字符串。该命令链中最高级的人（总裁）位于排序键字符串的开头。虽然通常不会显示排序键，但下面的查询会在输出中包含排序键，以便更容易理解输出。
每次迭代都应将排序键的长度增加相同的数量（相同的字符数），这样查询就会使用具有以下定义的名为 skey 的 UDF （用户定义函数）来生成排序键的长度一致的段：
CREATE OR REPLACE FUNCTION skey(ID VARCHAR) RETURNS VARCHAR AS $$ SUBSTRING('0000' || ID::VARCHAR, -4) || ' ' $$ ;下面是
SKEY函数的输出示例：SELECT skey(12); +----------+ | SKEY(12) | |----------| | 0012 | +----------+
下面是查询的最终版本。这会将每个经理的员工放在该经理的正下方，并根据员工的“级别”进行缩进：
WITH RECURSIVE managers -- Column list of the "view" (indent, employee_ID, manager_ID, employee_title, sort_key) AS -- Common Table Expression ( -- Anchor Clause SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title, skey(employee_ID) FROM employees WHERE title = 'President' UNION ALL -- Recursive Clause SELECT indent || '--- ', employees.employee_ID, employees.manager_ID, employees.title, sort_key || skey(employees.employee_ID) FROM employees JOIN managers ON employees.manager_ID = managers.employee_ID ) -- This is the "main select". SELECT indent || employee_title AS Title, employee_ID, manager_ID, sort_key FROM managers ORDER BY sort_key ; +----------------------------------+-------------+------------+-----------------+ | TITLE | EMPLOYEE_ID | MANAGER_ID | SORT_KEY | |----------------------------------+-------------+------------+-----------------| | President | 1 | NULL | 0001 | | --- Vice President Engineering | 10 | 1 | 0001 0010 | | --- --- Programmer | 100 | 10 | 0001 0010 0100 | | --- --- QA Engineer | 101 | 10 | 0001 0010 0101 | | --- Vice President HR | 20 | 1 | 0001 0020 | | --- --- Health Insurance Analyst | 200 | 20 | 0001 0020 0200 | +----------------------------------+-------------+------------+-----------------+
下一个查询演示如何引用层次结构中上一个（更高）级别的字段；请特别注意 mgr_title 列：
WITH RECURSIVE managers -- Column names for the "view"/CTE (employee_ID, manager_ID, employee_title, mgr_title) AS -- Common Table Expression ( -- Anchor Clause SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title FROM employees WHERE title = 'President' UNION ALL -- Recursive Clause SELECT employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title FROM employees JOIN managers ON employees.manager_ID = managers.employee_ID ) -- This is the "main select". SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title FROM managers ORDER BY manager_id NULLS FIRST, employee_ID ; +----------------------------+-------------+------------+----------------------------+ | TITLE | EMPLOYEE_ID | MANAGER_ID | MGR_TITLE | |----------------------------+-------------+------------+----------------------------| | President | 1 | NULL | NULL | | Vice President Engineering | 10 | 1 | President | | Vice President HR | 20 | 1 | President | | Programmer | 100 | 10 | Vice President Engineering | | QA Engineer | 101 | 10 | Vice President Engineering | | Health Insurance Analyst | 200 | 20 | Vice President HR | +----------------------------+-------------+------------+----------------------------+
部件分解¶
经理/员工层次结构并不是唯一一种可以存储在单个表中并使用递归 CTE 处理的可变深度层次结构。分层数据的另一个常见示例是“部件分解”，其中每个部件都可以与其子部件一起列出，每个子部件都可以与其子子部件一起列出。
例如，假设您的表包含分层数据，例如汽车的部件。您的汽车可能包含发动机、车轮等部件。其中许多部件包含子部件（例如，发动机可能包含燃油泵）。燃油泵可能包含电机、油管等。您可以使用递归 CTE 列出所有部件及其子部件。
有关生成部件分解的查询的示例，请参阅 WITH。
对递归 CTE 进行故障排除¶
递归 CTE 查询一直运行，直到成功或超时¶
此问题可能是由两种不同的情况引起的：
数据层次结构可能有一个循环。
您可能已经创建了一个无限循环。
原因 1：循环数据层次结构¶
如果您的数据层次结构包含一个循环（即它不是真正的树），则有多种可能的解决方案：
- 解决方案 1.1:
如果数据不应包含循环，请更正数据。
- 解决方案 1.2:
以某种方式限制查询（例如，限制输出的行数）。例如：
WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT N + 1 FROM t ) SELECT n FROM t LIMIT 10;
- 解决方案 1.3:
请勿使用包含递归的 CTE 的查询，因为递归需要分层数据。
原因 2：无限循环¶
如果 recursive_clause 中的投影子句从“父”（上一次迭代）而不是“子”（当前迭代）输出一个值，并且下一次迭代在应在联接中使用当前迭代的值时使用了该值，则可能会发生无限循环。
以下伪代码显示了一个近似示例：
CREATE TABLE employees (employee_ID INT, manager_ID INT, ...); INSERT INTO employees (employee_ID, manager_ID) VALUES (1, NULL), (2, 1); WITH cte_name (employee_ID, manager_ID, ...) AS ( -- Anchor Clause SELECT employee_ID, manager_ID FROM table1 UNION ALL SELECT manager_ID, employee_ID -- <<< WRONG FROM table1 JOIN cte_name ON table1.manager_ID = cte_name.employee_ID ) SELECT ...
在此示例中，递归子句在应具有当前/子值 (employee_id) 的列中传递其父值 (manager_id)。父级将在下一次迭代中显示为“当前”值，并将再次作为“当前”值传递给下一代，因此查询永远不会在层级中向下推进；它每次都保持相同的层级。
- 第 1 步:
假设锚点子句选择值
employee_id = 1和manager_id = NULL。CTE：
employee_ID manager_ID ----------- --------- 1 NULL
- 第 2 步:
在递归子句的第一次迭代期间，
table1中的employee_id = 2且manager_id = 1。CTE：
employee_ID manager_ID ----------- ---------- 1 NULL
table1：employee_ID manager_ID ----------- ---------- ... 2 1 ...
递归子句中的联接结果：
table1.employee_ID table1.manager_ID cte.employee_ID cte.manager_ID ----------------- ----------------- --------------- -------------- ... 2 1 1 NULL ...
投影：
employee_ID manager_ID ----------- ---------- ... 2 1 ...
但是，由于
employee_id和manager_id列在投影中颠倒，因此查询的实际输出（以及下一次迭代开始时 CTE 的内容）是：employee_ID manager_ID ----------- ---------- ... 1 2 -- Because manager and employee IDs reversed ...
- 第 3 步:
在递归子句的第 2 次迭代期间：
CTE：
employee_ID manager_ID ----------- ---------- 1 2
table1：employee_ID manager_ID ----------- ---------- ... 2 1 ...
递归子句中的联接结果：
table1.employee_ID table1.manager_ID cte.employee_ID cte.manager_ID ----------------- ----------------- --------------- -------------- ... 2 1 1 2 ...
投影：
employee_ID manager_ID ----------- ---------- ... 2 1 ...
查询结果（下次迭代开始时 CTE 的内容）：
employee_ID manager_ID ----------- ---------- ... 1 2 -- Because manager and employee IDs reversed ...
如您所见，在第 2 次迭代结束时，CTE 中的行与迭代开始时的行相同：
employee_id为1。manager_id为2。
因此，下一次迭代期间的联接结果将与当前迭代期间联接的结果相同，并且查询会无限循环。
如果您创建了无限循环：
- 解决方案 2:
确保递归子句以正确的顺序传递正确的变量。
还要确保递归子句中的 JOIN 条件正确。通常情况下，“当前”行的父行应联接到父行的子值/当前值。