跳到主要内容

通用表表达式

通用表表达式（Common Table Expressions，CTE）是一个命名的临时结果集，作用范围是当前语句，不实际作为对象存储，仅在查询执行期间被使用；与派生表不同，CTE 可以是自引用，也可以在同一查询中多次引用。

使用场景：

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

seekdb 支持非递归的 CTE 和递归的 CTE。

CTE 语法​

通用表表达式是 DML 语句语法的可选部分，使用 WITH 子句定义。如果 WITH 子句包含多个子句可以使用逗号分隔。每个子句提供一个子查询以产生结果集，并将子查询与名称相关联。语法如下：

 WITH [RECURSIVE]
      cte_name [(column_name [, column_name] ...)] AS (subquery)
      [, cte_name [(column_name [, column_name] ...)] AS (subquery)] ...

参数说明​

参数项描述
[RECURSIVE]可选的关键字，用于指定是否创建递归的 CTE。
  • 递归通用表表达式：指定 RECURSIVE 关键字。递归通用表表达式是指 CTE 中引用自身的表达式，用于处理具有递归结构的数据，例如树形结构、图形等。递归 CTE 在定义中包含一个基本查询（起始条件），然后在该基本查询的结果上进行递归操作，直到满足停止条件为止。
  • 非递归通用表表达式：不指定 RECURSIVE 关键字。非递归通用表表达式是指 CTE 中不引用自身的表达式，它只用于构建一次性的临时结果集，不涉及递归操作。
cte_name命名通用表表达式，可被包含 WITH 子句的的表引用。
column_name选的列名列表，用于为 CTE 中的列指定别名。这使得在主查询中，您可以使用更易读的列名。
AS(subquery)即，CTE 子查询，用于生成 CTE 结果集。AS 后面必须带括号。

如果在 CTE 名称后面有带括号的名称列表，那么这些名称就是列名称，数量必须与 CTE 中的 SELECT 语句中的列数相同。如果没有指定列名称，那么列名将来自于 AS(subquery) 部分中第一个 SELECT List。

WITH 子句适用场景​

在以下场景允许使用 WITH 子句：

  • 在 SELECT 语句的开头。

    WITH ... SELECT ...

  • 在子查询（包括派生表子查询）的开头。

    SELECT ... WHERE id IN (WITH ... SELECT ...) ...
    SELECT * FROM (WITH ... SELECT ...) AS dt ...

  • 对于包含 SELECT 语句的语句，紧接在 SELECT 之前。

    INSERT ... WITH ... SELECT ...
    REPLACE ... WITH ... SELECT ...
    CREATE TABLE ... WITH ... SELECT ...
    CREATE VIEW ... WITH ... SELECT ...

同一级别只允许一个 WITH 子句。如果 WITH 子句包含多个子句可以使用逗号分隔。

WITH cte1 AS (...), cte2 AS (...) SELECT ...

WITH 子句可以定义一个或多个通用表表达式，但每个 CTE 名称对于该子句必须是唯一的。以下示例是非法的：

WITH cte1 AS (...), cte1 AS (...) SELECT ...

递归 CTE 的结构​

递归 CTE 具有以下结构：

  • 如果 WITH 子句中的 CTE 引用自身，则 WITH 子句必须以 WITH RECURSIVE 开头。否则不要求必须带 RECURSIVE。

  • 递归 CTE 子查询有两部分，由 UNION [ALL | DISTINCT] 分隔：

    信息

    • UNION 或者 UNION DISTINCT：用于将两个或多个 SELECT 语句的结果集合并为一个集合，并去除重复的行。

    • UNION ALL：用于将两个或多个 SELECT 语句的结果集合并为一个集合，不去除重复的行。

    SELECT ...      -- 返回初始行集
    UNION ALL
    SELECT ...      -- 返回额外的行集

    第一个 SELECT 为 CTE 生成一个或多个初始行，并且不引用 CTE 名称。第二个 SELECT 产生额外的行并通过在其 FROM 子句中引用 CTE 名称进行递归。当这部分不产生新行时，递归结束。因此，递归 CTE 由一个非递归 SELECT 部分和一个递归 SELECT 部分组成。每个 SELECT 部分可以是多个 SELECT 语句的联合。

  • CTE 结果列的类型仅从非递归 SELECT 部分的列类型中推断出来，并且所有列都可以为空。类型的确定会忽略递归 SELECT 部分。

  • 递归部分的每次迭代仅对前一次迭代产生的行进行操作。如果递归部分有多个查询块，则每个查询块的迭代按未指定的顺序进行调度，并且每个查询块操作的行是其上一次迭代或自上次迭代结束后由其他查询块生成的行。

示例如下：

WITH RECURSIVE cte1 (n) AS
(
  SELECT 1                /*非递归部分，它检索单个行以生成初始行集*/
  UNION ALL
  SELECT n + 2 FROM cte1 WHERE n < 10     /*递归部分，生成一个比前一行集中的 n 值大 2 的新值，直至 n 不小于 10*/
)
SELECT * FROM cte1;

使用限制​

以下语法约束适用于递归 CTE 子查询：

  • 递归 SELECT 部分不得包含以下结构：

    • 聚合函数，例如 SUM()

    • 窗口函数

    • GROUP BY

    • ORDER BY

    • DISTINCT

  • 递归 SELECT 部分必须仅在其 FROM 子句中的子查询中引用一次。它可以引用 CTE 以外的表并与 CTE 进行连接。如果在这样的连接中使用，则 CTE 不得位于 LEFT JOIN 的右侧。

对于递归 CTE，EXPLAIN 显示的成本估算代表每次迭代的成本，可能与总成本有很大不同。优化器无法预测迭代次数，因为它无法预测 WHERE 子句在什么时候变为 False。

  • 递归 SELECT 与非递归 SELECT 之间不支持包含 LIMIT 结构。

示例​

下面是一个简单的例子，我们将创建一个学生档案表，该表包含学生的 ID、姓名和他们的导师 ID），来展示递归 CTE 和非递归 CTE 的区别。

首先，我们建立学生档案表并插入一些数据：

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    mentor_id INT,
    FOREIGN KEY (mentor_id) REFERENCES student(student_id)
);
Query OK, 0 rows affected (0.135 sec)

INSERT INTO student (student_id, name, mentor_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 3);
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0

在这个数据模型中，Alice 是顶层学生，没有导师。Bob 和 Charlie 是 Alice 的学生，David 是 Bob 的学生，Eve 是 Charlie 的学生。

非递归 CTE 示例​

非递归的 CTE 不会引用自身。例如，如果我们想要选出所有 Alice 的直接学生（即第一级学生），我们可以使用这样一个非递归 CTE：

WITH Alice_Students AS (
    SELECT * FROM student WHERE mentor_id = 1
)
SELECT * FROM Alice_Students;

执行结果将是：

+------------+---------+-----------+
| student_id | name    | mentor_id |
+------------+---------+-----------+
|          2 | Bob     |         1 |
|          3 | Charlie |         1 |
+------------+---------+-----------+
2 rows in set (0.003 sec)

递归 CTE 示例​

现在，如果我们想要找到 Alice 的所有直接和间接学生（即所有学生层级），我们需要使用递归 CTE。递归 CTE 会引用自身来逐级向下查询。

WITH RECURSIVE Student_Hierarchy AS (
    -- 锚点成员：选择 Alice 作为起始点
    SELECT student_id, name, mentor_id FROM student WHERE mentor_id IS NULL
    UNION ALL
    -- 递归成员：基于前一级学生的 ID 来选择直接学生
    SELECT s.student_id, s.name, s.mentor_id
    FROM student s
    INNER JOIN Student_Hierarchy sh ON s.mentor_id = sh.student_id
)
SELECT * FROM Student_Hierarchy;

这个递归 CTE 首先选择了 Alice 作为起始点，然后递归地选择每个学生的直接学生，直到没有更多学生为止。

执行结果将是：

+------------+---------+-----------+
| student_id | name    | mentor_id |
+------------+---------+-----------+
|          1 | Alice   |      NULL |
|          3 | Charlie |         1 |
|          2 | Bob     |         1 |
|          5 | Eve     |         3 |
|          4 | David   |         2 |
+------------+---------+-----------+
5 rows in set (0.008 sec)

在这个例子中，递归 CTE 允许我们遍历学生的层级结构，而非递归 CTE 仅选择了一个固定级别的学生。

Contents

编辑此页