tencent cloud

云数据库 MySQL

动态与公告
产品动态
产品公告
新手指引
产品简介
产品概述
产品优势
应用场景
数据库架构
隔离策略
经济型实例
产品功能列表
数据库实例
高可用性（多可用区）
地域和可用区
自研内核
内核概述
内核版本更新动态
功能类特性
性能类特性
安全类特性
稳定类特性
TXRocks 引擎
内核问题检查与修复
购买指南
计费概述
选型指南
购买方式
续费说明
欠费说明
退费说明
按量转包年包月
调整实例费用说明
备份空间收费说明
数据库审计计费说明
数据库代理商业化计费和活动说明
数据库代理计费周期说明
查看费用账单
快速入门
入门概述
创建 MySQL 实例
连接 MySQL 实例
SQL 洞察（原数据库审计）
数据库审计简介
查看审计实例列表
开通审计服务
查看审计日志
日志投递
配置事后告警
修改审计规则
修改审计服务
关闭审计服务
审计规则模板
SQL 审计规则（旧版）
查看审计任务
授权子用户使用数据库审计
MySQL 云盘版
MySQL 云盘版概述
创建 MySQL 云盘版实例
维护管理实例
查看实例监控
调整实例配置
其他功能相关操作
迁移或升级至 MySQL 云盘版
操作指南
使用限制
操作总览
维护管理实例
升级实例
CPU 弹性扩容
只读/灾备实例
数据库代理
数据库管理（DMC）
账号管理
参数配置
备份与回档
数据迁移
网络与安全
监控与告警
日志中心
只读分析引擎
标签
实践教程
腾讯云 MySQL 帮助实现 MySQL 5.7升级至 MySQL 8.0
MySQL 5.6升级到 MySQL 5.7方法和说明
云数据库 MySQL 数据库审计等保实践
构建全场景高可用架构
云数据库 MySQL 使用规范
应用程序配置自动重连功能
MySQL 主实例参数修改的影响
MyISAM 自动转换为 InnoDB 引擎限制
为云数据库 MySQL 创建 VPC
使用云数据库 MySQL 提高业务负载能力
两地三中心灾备建设
读写分离扩展云数据库 MySQL 性能
使用 DTS 将 InnoDB 数据迁移至 RocksDB
构建 LAMP 堆栈 Web 应用程序
构建 Drupal 网站
通过 Python 语言使用 MySQL API
主备实例查询数据不一致
白皮书
性能白皮书
安全白皮书
故障处理
连接相关
性能相关
实例数据同步延迟
设置大小写不敏感失败
通过命令获取 slow_query_log_file 失败
API 文档
History
Introduction
API Category
Instance APIs
调用方式
Data Import APIs
Database Proxy APIs
数据库审计相关接口
Security APIs
Task APIs
Backup APIs
Account APIs
Rollback APIs
Parameter APIs
Database APIs
Monitoring APIs
Log-related API
Data Types
Error Codes
常见问题
选型相关
计费相关
备份相关
回档相关
连接登录
参数修改
升级相关
账号权限
性能内存
运维相关
数据迁移
功能特性
控制台相关
日志相关
事件相关
数据库审计
实例切换影响
API 2.0 切换 3.0 指引
相关协议
服务等级协议
服务条款
通用参考
标准与认证
联系我们
词汇表
文档云数据库 MySQL操作指南只读分析引擎执行查询CTE 语法使用说明

CTE 语法使用说明

PDF
聚焦模式
字号
最后更新时间： 2025-11-28 18:24:45
云数据库 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 在执行时的性能。
4.2506.x
在4.2506.x 版本中，只读分析引擎可以支持 Recursive 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)
上一篇: SELECT 语句说明下一篇: 窗口函数使用说明

帮助和支持

本页内容是否解决了您的问题？

填写满意度调查问卷，共创更好文档体验。

文档反馈