公共表表达式 (CTE)
公用表表达式(CTE,Common table expression) 是一个命名的临时结果集,仅在单个 SQL 语句 (例如 SELECT
,INSERT
,UPDATE
或 DELETE
) 的执行范围内存在。
与派生表类似,CTE 不作为对象存储,仅在查询执行期间持续;与派生表不同,CTE 可以是自引用,也可以在同一查询中多次引用。此外,与派生表相比,CTE 提供了更好的可读性和性能。
应用场景:
- CTE 可以用于多个地方复用相同的子查询,避免重复编写相同的逻辑。
- 可以用于简化递归查询,例如查找树形结构数据。
- 可以将复杂的查询拆分为多个较小的部分,使查询逻辑更清晰易懂。
通用表表达式分为非递归和递归两种类型:
- 非递归公共表达式:是指 CTE 中不引用自身的表达式,它只用于构建一次性的临时结果集,不涉及递归操作。非递归 CTE 语法如下:
WITH <query_name> AS (
<query_definition>
)
SELECT ... FROM <query_name>;
- 递归公共表达式:是指 CTE 中引用自身的表达式,用于处理具有递归结构的数据,例如树形结构、图形等。递归 CTE 在定义中包含一个基本查询(起始条件),然后在该基本查询的结果上进行递归操作,直到满足停止条件为止。递归 CTE 语法如下:
WITH RECURSIVE <query_name> AS (
<query_definition>
)
SELECT ... FROM <query_name>;
开始前准备
你需要确认在开始之前,已经完成了以下任务:
已完成单机部署 MatrixOne。
CTE 语句使用示例
假设我们要创建一个名为 EMPLOYEES
的表,其中包含员工的层次结构关系,然后使用非递归 Common Table Expression(CTE)和递归 CTE 分别查询员工层次结构。
首先,我们创建一个 EMPLOYEES
表并插入一些示例数据:
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID INT PRIMARY KEY,
NAME VARCHAR(50),
MANAGER_ID INT
);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, NAME, MANAGER_ID) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3),
(7, 'Grace', 3),
(8, 'Hannah', 4),
(9, 'Ian', 4);
接下来,我们使用递归 CTE 查询员工层次结构:
WITH RECURSIVE EmployeeHierarchy AS (
SELECT EMPLOYEE_ID, NAME, MANAGER_ID, 0 AS LEVEL
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
UNION ALL
SELECT e.EMPLOYEE_ID, e.NAME, e.MANAGER_ID, eh.LEVEL + 1
FROM EMPLOYEES e
INNER JOIN EmployeeHierarchy eh ON e.MANAGER_ID = eh.EMPLOYEE_ID
)
SELECT * FROM EmployeeHierarchy;
+-------------+--------+------------+-------+
| employee_id | name | manager_id | level |
+-------------+--------+------------+-------+
| 1 | Alice | NULL | 0 |
| 2 | Bob | 1 | 1 |
| 3 | Carol | 1 | 1 |
| 4 | David | 2 | 2 |
| 5 | Eve | 2 | 2 |
| 6 | Frank | 3 | 2 |
| 7 | Grace | 3 | 2 |
| 8 | Hannah | 4 | 3 |
| 9 | Ian | 4 | 3 |
+-------------+--------+------------+-------+
9 rows in set (0.01 sec)
然后,我们使用非递归 CTE 查询员工信息:
WITH EmployeeInfo AS (
SELECT EMPLOYEE_ID, NAME, MANAGER_ID
FROM EMPLOYEES
)
SELECT * FROM EmployeeInfo;
+-------------+--------+------------+
| employee_id | name | manager_id |
+-------------+--------+------------+
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | David | 2 |
| 5 | Eve | 2 |
| 6 | Frank | 3 |
| 7 | Grace | 3 |
| 8 | Hannah | 4 |
| 9 | Ian | 4 |
+-------------+--------+------------+
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)