云数据库 MySQL CTE 语法使用说明

云数据库 MySQL 在5.7版本中无法支持 CTE 语法。仅8.0版本后的行存引擎才支持 CTE 语法，但对于只读分析引擎而言，无论是5.7版本还是8.0版本，在只读分析引擎中均能正常支持 CTE 语法。
支持情况
1.2404.x
在1.2404.x 版本中，只读分析引擎仅支持 Non-Recursive CTE，使用时需要添加Hint /*+ MERGE() */，否则可能无法生成正确的执行计划。在后续版本中，只读分析引擎将逐步支持 Recursive CTE，并且会优化 CTE 的执行性能。
2.2410.x
在2.2410.x 版本中，只读分析引擎仍然仅支持 Non-Recursive CTE。但是当前版本执行 CTE 语法时已经无需添加Hint /*+ MERGE() */即可正常执行。同时在2.2410.x 版本中，也支持了流式执行的方式，大幅度优化了 CTE 在执行时的性能。
CTE 简介
公共表达式（Common Table Expressions，CTE）是 SQL 标准的一部分，通常被称为“WITH 子句”。CTE 在 SQL：1999标准首次引入，旨在提供一种简洁而强大的方法来定义临时结果集。这些结果集可以在单个 SQL 语句中被多次引用，大大提高了查询的可读性和可维护性。
WITH 子句使用示例：
-- Start defining CTE.
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
-- End defining CTE.
SELECT  *
FROM CustomerCTE; -- Reference the CTE.
CTE 的优势
在复杂的 SQL 查询中，CTE 提供了许多优势。
简化查询：CTE 可以组织和简化复杂的 SQL 语句，提高可维护性。例如，在需要多次引用相同子查询的情况下，CTE 可以避免重复代码，从而使查询更加清晰。
提高代码可读性：使用有意义的名称来表示中间结果，使 SQL 更易理解。
避免重复查询：CTE 允许定义临时的结果集，这些结果集可以在一条 SQL 中多次引用，从而避免了相同操作的重复执行。
递归查询：CTE 支持递归查询，能够处理层次结构数据（如员工组织结构）的查询需求。在处理树状结构数据时非常有用。
语法结构
CTE 的语法结构如下。
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
参数项
描述
WITH 关键字
表示 CTE 定义的开始。
[RECURSIVE]
可选关键字，如果包含 RECURSIVE，表示允许在 CTE 中引用自身，用于创建递归查询。
cte_name
为 CTE 指定的名称，可以在后续的查询中被引用。
[(col_name [，col_name] ...)]
可选的列名列表，为 CTE 的结果集指定列名。如果省略，将使用子查询中的列名。
AS (subquery)
CTE 内部的子查询，定义 CTE 的内容。
逗号和额外的 CTEs
在一个 WITH 子句中，可以定义多个 CTEs，用逗号分隔。每个额外的 CTE 都遵循相同的结构：cte_name [(col_name ...)] AS (subquery)。
非递归 CTE（Non-Recursive CTE）
在非递归 CTE 中，CTE 仅引用其他表或者之前定义的 CTE，而不会引用自身。它适用于多步查询的分解，通过中间层次计算逐步构建最终查询结果。
WITH
	cte1 AS (SELECT * FROM t1, t2),
    cte2 AS (SELECT i1, i2 FROM cte1 WHERE i3 > 10)
    cte3 AS (SELECT * FROM cte2, t3 WHERE cte2.i1 = t3.i1)
SELECT * FROM cte3;
递归 CTE（Recursive CTE）
在递归 CTE 中，CTE 会引用自身。Recursive CTE 常用于处理树结构或图结构的数据查询，例如计算阶乘，生成序列或遍历层级关系。
Recursive CTE 由 Seed Part Subquery，Union Type，Recursive Part Subquery 三部分组成。Seed Part Subquery 不引用自身，Recursive Part Subquery 一定引用自身。
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1 -- Seed Part Subquery
    UNION ALL -- Union Type
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 -- Recursive Part Subquery
)
SELECT n, fact FROM cte;
示例
计算阶乘
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1
    UNION ALL
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5
)
SELECT n, fact FROM cte;
在这个示例中，递归部分 UNION ALL SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 会重复调用自身，直到 n 达到5，递归部分输出空行时，结束递归。
遍历树结构
假设我们有一张表 employees，包含员工的层级关系，其中 id 是员工的唯一标识，name 是员工姓名，manager_id 是该员工的上级员工 id。
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);
﻿
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'Manager 1', 1),
(3, 'Manager 2', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);
递归 CTE 用于遍历员工层次结构，从上到下获取所有下属：
﻿
WITH RECURSIVE employee_hierarchy AS (
    -- 基础情况：从 CEO 开始
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
﻿
    UNION ALL
﻿
    -- 递归情况：找到每个员工的下属
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON eh.id = e.manager_id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, manager_id;
﻿
-- Result
┌───────┬────────────┬────────────┬───────┐
│  id   │    name    │ manager_id │ level │
│ int32 │  varchar   │   int32    │ int32 │
├───────┼────────────┼────────────┼───────┤
│     1 │ CEO        │            │     1 │
│     2 │ Manager 1  │          1 │     2 │
│     3 │ Manager 2  │          1 │     2 │
│     4 │ Employee 1 │          2 │     3 │
│     5 │ Employee 2 │          2 │     3 │
│     6 │ Employee 3 │          3 │     3 │
└───────┴────────────┴────────────┴───────┘
基础 CTE
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
SELECT /*+ MERGE() */ * 
FROM CustomerCTE;
多个 CTE
WITH 
CTE1 AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
),
CTE2 AS (
    SELECT ss_item_sk, ss_customer_sk, ss_sold_date_sk, ss_sales_price
    FROM store_sales
)
SELECT /*+ MERGE() */ CTE1.first_name, CTE1.last_name, CTE2.ss_sales_price
FROM CTE1
JOIN CTE2 ON CTE1.customer_id = CTE2.ss_customer_sk;
定义了两个 CTE：CTE1 和 CTE2。
在最终查询中使用这两个 CTE 的结果集进行 Join 操作。
执行结果：
+------------+-----------+----------------+
| first_name | last_name | ss_sales_price |
+------------+-----------+----------------+
| John       | Doe       |          45.99 |
| Jane       | Smith     |          32.50 |
| Michael    | Johnson   |          78.25 |
| Emily      | Brown     |          19.99 |
| David      | Wilson    |          55.00 |
| John       | Doe       |          67.75 |
| Jane       | Smith     |          22.99 |
| Michael    | Johnson   |          41.50 |
| Emily      | Brown     |          89.99 |
| David      | Wilson    |          33.25 |
+------------+-----------+----------------+
10 rows in set (0.12 sec)
嵌套 CTE
WITH SalesSummary AS (
    SELECT ss_customer_sk, SUM(ss_net_paid) AS total_spent
    FROM store_sales
    GROUP BY ss_customer_sk
),
TopCustomers AS (
    SELECT ss_customer_sk, total_spent
    FROM SalesSummary
    WHERE total_spent > 1000  -- 假设设置一个阈值, 例如消费超过 1000 的顾客
),
CustomerDetails AS (
    SELECT c.customer_id, c.first_name, c.last_name, tc.total_spent
    FROM customer c
    JOIN TopCustomers tc ON c.customer_id = tc.ss_customer_sk
)
SELECT /*+ MERGE() */ * 
FROM CustomerDetails;
SalesSummary 计算每个顾客的总消费。
TopCustomers 从 SalesSummary 结果集中筛选出消费超过1000的顾客。
CustomerDetails 将 customer 表中的顾客信息与 TopCustomers 中的结果集进行连接。
最终的 SELECT 查询从 CustomerDetails 中提取所有数据。
执行结果：
+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | total_spent  |
+-------------+------------+-----------+--------------+
|        1001 | John       | Doe       |      1523.75 |
|        1002 | Jane       | Smith     |      2105.50 |
|        1003 | Michael    | Johnson   |      1789.99 |
|        1004 | Emily      | Brown     |      1650.25 |
|        1005 | David      | Wilson    |      1875.00 |
|        1006 | Sarah      | Davis     |      2250.75 |
|        1007 | Robert     | Taylor    |      1955.50 |
|        1008 | Jennifer   | Anderson  |      1725.25 |
|        1009 | William    | Thomas    |      2015.00 |
|        1010 | Lisa       | Jackson   |      1890.75 |
+-------------+------------+-----------+--------------+
10 rows in set (0.15 sec)
参数项	描述
WITH 关键字	表示 CTE 定义的开始。
[RECURSIVE]	可选关键字，如果包含 RECURSIVE，表示允许在 CTE 中引用自身，用于创建递归查询。
cte_name	为 CTE 指定的名称，可以在后续的查询中被引用。
[(col_name [，col_name] ...)]	可选的列名列表，为 CTE 的结果集指定列名。如果省略，将使用子查询中的列名。
AS (subquery)	CTE 内部的子查询，定义 CTE 的内容。
逗号和额外的 CTEs	在一个 WITH 子句中，可以定义多个 CTEs，用逗号分隔。每个额外的 CTE 都遵循相同的结构：cte_name [(col_name ...)] AS (subquery)。
CTE 语法使用说明

本页目录：

支持情况

CTE 简介

CTE 的优势

语法结构

非递归 CTE（Non-Recursive CTE）

递归 CTE（Recursive CTE）

示例

基础 CTE

多个 CTE

嵌套 CTE
