tencent cloud

TDSQL-C MySQL 版

动态与公告
产品动态
产品公告
新手指引
产品简介
产品概述
产品优势
应用场景
产品架构
产品规格
实例类型
产品功能列表
数据库版本
地域和可用区
常用概念
使用限制
使用规范建议
自研内核
内核概述
内核版本更新动态
内核优化版本
功能类特性
性能类特性
安全类特性
稳定性特性
分析引擎特性
内核问题检查与修复
购买指南
计费概述
产品价格
创建集群
变配说明
续费说明
欠费说明
退费说明
按量转包年包月
按量转 Serverless
增值服务计费说明
查看费用账单
快速入门
数据库审计
简介
查看审计实例列表
开通审计服务
查看审计日志
日志投递
配置事后告警
修改审计规则
修改审计服务
关闭审计服务
审计规则模板
查看审计任务
授权子用户使用数据库审计
Serverless 服务
Serverless 简介
创建和管理 Serverless 版集群
弹性管理工具
Serverless 资源包
多可用区部署
配置变更
常见问题
Serverless 成本预估器
操作指南
操作总览
控制台切换集群页面视图
数据库连接
实例管理
配置变更
实例形态管理
集群管理
只读实例管理
数据库代理
账号管理
数据库管理
数据库管理工具（DMC）
参数配置
多可用区部署
全球数据库
备份与恢复
操作日志
迁移数据
并行查询
列存索引 CSI
分析引擎
数据库安全和加密
监控与告警
SQL 基本操作
使用 SCF 连接 TDSQL-C MySQL 版
标签
实践教程
TDSQL-C MySQL 版数据库审计等保实践
通过 DTS 升级数据库版本 MySQL5.7至8.0
TDSQL-C MySQL 版使用规范
新版本控制台
数据库代理多连接地址实现多 RO 组
数据库代理的优势
如何选择存储空间计费模式
通过 DTS 构建异地灾备
为集群创建 VPC
如何进行数据恢复
如何解决 CPU 使用率高的问题
如何授权子用户查看监控
白皮书
安全白皮书
性能白皮书
故障处理
连接相关
性能相关
API 文档
History
Introduction
API Category
Making API Requests
Instance APIs
Multi-Availability Zone APIs
Other APIs
Audit APIs
Database Proxy APIs
Backup and Recovery APIs
Parameter Management APIs
Billing APIs
serverless APIs
Resource Package APIs
Account APIs
Performance Analysis APIs
Data Types
Error Codes
常见问题
基础概念
购买与计费
兼容与格式
连接与网络
功能特性
控制台操作
数据库表
性能与日志
数据库审计
TDSQL-C MySQL 版和云数据库 MySQL 有什么区别
相关协议
服务等级协议
服务条款
TDSQL-C 政策
隐私政策
数据处理和安全协议
通用参考
标准与认证
词汇表
联系我们
文档TDSQL-C MySQL 版操作指南分析引擎执行查询CTE 语法使用说明

CTE 语法使用说明

PDF
聚焦模式
字号
最后更新时间： 2025-12-03 09:55:27
TDSQL-C 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 语句说明下一篇: 窗口函数使用说明

帮助和支持

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

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

文档反馈