公共表表达式 (CTE)

公用表表达式（CTE,Common table expression) 是一个命名的临时结果集，仅在单个 SQL 语句 (例如 SELECT，INSERT，UPDATE 或 DELETE) 的执行范围内存在。

与派生表类似，CTE 不作为对象存储，仅在查询执行期间持续；与派生表不同，CTE 可以是自引用，也可以在同一查询中多次引用。此外，与派生表相比，CTE 提供了更好的可读性和性能。

应用场景：

  • CTE 可以用于多个地方复用相同的子查询，避免重复编写相同的逻辑。
  • 可以用于简化递归查询，例如查找树形结构数据。
  • 可以将复杂的查询拆分为多个较小的部分，使查询逻辑更清晰易懂。

通用表表达式分为非递归和递归两种类型：

  • 非递归公共表达式：是指 CTE 中不引用自身的表达式，它只用于构建一次性的临时结果集，不涉及递归操作。非递归 CTE 语法如下：
  1. WITH <query_name> AS (
  2.     <query_definition>
  3. )
  4. SELECT ... FROM <query_name>;
  • 递归公共表达式：是指 CTE 中引用自身的表达式，用于处理具有递归结构的数据，例如树形结构、图形等。递归 CTE 在定义中包含一个基本查询（起始条件），然后在该基本查询的结果上进行递归操作，直到满足停止条件为止。递归 CTE 语法如下：
  1. WITH RECURSIVE <query_name> AS (
  2.     <query_definition>
  3. )
  4. SELECT ... FROM <query_name>;

开始前准备

你需要确认在开始之前，已经完成了以下任务：

已完成单机部署 MatrixOne。

CTE 语句使用示例

假设我们要创建一个名为 EMPLOYEES 的表，其中包含员工的层次结构关系，然后使用非递归 Common Table Expression（CTE）和递归 CTE 分别查询员工层次结构。

首先，我们创建一个 EMPLOYEES 表并插入一些示例数据：

  1. CREATE TABLE EMPLOYEES (
  2.     EMPLOYEE_ID INT PRIMARY KEY,
  3.     NAME VARCHAR(50),
  4.     MANAGER_ID INT
  5. );
  7. INSERT INTO EMPLOYEES (EMPLOYEE_ID, NAME, MANAGER_ID) VALUES
  8.     (1, 'Alice', NULL),
  9.     (2, 'Bob', 1),
  10.     (3, 'Carol', 1),
  11.     (4, 'David', 2),
  12.     (5, 'Eve', 2),
  13.     (6, 'Frank', 3),
  14.     (7, 'Grace', 3),
  15.     (8, 'Hannah', 4),
  16.     (9, 'Ian', 4);

接下来，我们使用递归 CTE 查询员工层次结构：

  1. WITH RECURSIVE EmployeeHierarchy AS (
  2.     SELECT EMPLOYEE_ID, NAME, MANAGER_ID, 0 AS LEVEL
  3.     FROM EMPLOYEES
  4.     WHERE MANAGER_ID IS NULL
  6.     UNION ALL
  8.     SELECT e.EMPLOYEE_ID, e.NAME, e.MANAGER_ID, eh.LEVEL + 1
  9.     FROM EMPLOYEES e
  10.     INNER JOIN EmployeeHierarchy eh ON e.MANAGER_ID = eh.EMPLOYEE_ID
  11. )
  12. SELECT * FROM EmployeeHierarchy;
  13. +-------------+--------+------------+-------+
  14. | employee_id | name   | manager_id | level |
  15. +-------------+--------+------------+-------+
  16. |           1 | Alice  |       NULL |     0 |
  17. |           2 | Bob    |          1 |     1 |
  18. |           3 | Carol  |          1 |     1 |
  19. |           4 | David  |          2 |     2 |
  20. |           5 | Eve    |          2 |     2 |
  21. |           6 | Frank  |          3 |     2 |
  22. |           7 | Grace  |          3 |     2 |
  23. |           8 | Hannah |          4 |     3 |
  24. |           9 | Ian    |          4 |     3 |
  25. +-------------+--------+------------+-------+
  26. 9 rows in set (0.01 sec)

然后，我们使用非递归 CTE 查询员工信息：

  1. WITH EmployeeInfo AS (
  2.     SELECT EMPLOYEE_ID, NAME, MANAGER_ID
  3.     FROM EMPLOYEES
  4. )
  5. SELECT * FROM EmployeeInfo;
  6. +-------------+--------+------------+
  7. | employee_id | name   | manager_id |
  8. +-------------+--------+------------+
  9. |           1 | Alice  |       NULL |
  10. |           2 | Bob    |          1 |
  11. |           3 | Carol  |          1 |
  12. |           4 | David  |          2 |
  13. |           5 | Eve    |          2 |
  14. |           6 | Frank  |          3 |
  15. |           7 | Grace  |          3 |
  16. |           8 | Hannah |          4 |
  17. |           9 | Ian    |          4 |
  18. +-------------+--------+------------+
  19. 9 rows in set (0.00 sec)

我们使用了一个递归 CTE EmployeeHierarchy，它首先选择顶层经理（MANAGER_ID IS NULL），然后通过递归连接找到每个员工的直接下属，同时跟踪层次级别。这样，我们就可以通过查询 CTE 来获取员工层次结构的详细信息。

非递归 CTE 示例只是从 EMPLOYEES 表中选择所有员工的基本信息，包括 EMPLOYEE_ID、NAME 和 MANAGER_ID。

注意，递归 CTE 需要使用 RECURSIVE 关键字来声明。

更多关于 CTE 的使用文档，参考 WITH (Common Table Expressions)