SQL CTE(公用表表达式)用法：SQL Ultimate Course复杂查询简化-开发者社区

SQL CTE(公用表表达式)用法：SQL Ultimate Course复杂查询简化

【免费下载链接】sql-ultimate-courseThe most comprehensive SQL guide from a real-world expert! Learn everything from basics to advanced queries, optimizations, and real-world SQL项目地址: https://gitcode.com/gh_mirrors/sq/sql-ultimate-course

SQL Ultimate Course是一套全面的SQL学习指南，从基础到高级查询、优化和实际应用场景全覆盖。本文将重点介绍如何使用公用表表达式（CTE）简化复杂SQL查询，提升代码可读性和维护性。

什么是SQL CTE？

公用表表达式（CTE）是一种临时结果集，可在SQL语句中多次引用，类似于子查询但更具可读性和可维护性。CTE通过WITH子句定义，主要分为两种类型：

非递归CTE：用于简单的数据聚合、筛选和转换
递归CTE：能够处理层次结构数据和生成序列，通过自引用实现递归逻辑

为什么使用CTE？

相比传统子查询，CTE提供以下优势：

提高可读性：将复杂查询分解为逻辑块，便于理解和维护
支持递归：轻松处理树形结构数据（如组织架构、分类层级）
代码复用：在单个查询中多次引用相同的CTE定义
简化维护：修改CTE定义即可影响所有引用位置，减少重复劳动

非递归CTE实战案例

非递归CTE适用于大多数常规查询场景，特别适合数据聚合和多步骤计算。以下是一个典型的客户分析场景：

-- 步骤1: 计算每个客户的总销售额 WITH CTE_Total_Sales AS ( SELECT CustomerID, SUM(Sales) AS TotalSales FROM Sales.Orders GROUP BY CustomerID ), -- 步骤2: 查找每个客户的最后订单日期 CTE_Last_Order AS ( SELECT CustomerID, MAX(OrderDate) AS Last_Order FROM Sales.Orders GROUP BY CustomerID ) -- 主查询: 关联多个CTE结果 SELECT c.CustomerID, c.FirstName, c.LastName, cts.TotalSales, clo.Last_Order FROM Sales.Customers AS c LEFT JOIN CTE_Total_Sales AS cts ON cts.CustomerID = c.CustomerID LEFT JOIN CTE_Last_Order AS clo ON clo.CustomerID = c.CustomerID;

在SQL Ultimate Course的scripts/19_Common_Table_Expressions_CTE.sql文件中，还提供了更多高级用法，如客户排名和客户分群：

-- 客户排名CTE CTE_Customer_Rank AS ( SELECT CustomerID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS CustomerRank FROM CTE_Total_Sales ), -- 客户分群CTE CTE_Customer_Segments AS ( SELECT CustomerID, TotalSales, CASE WHEN TotalSales > 100 THEN 'High' WHEN TotalSales > 80 THEN 'Medium' ELSE 'Low' END AS CustomerSegments FROM CTE_Total_Sales )

递归CTE应用场景

递归CTE是处理层次结构数据的强大工具。让我们看看两个常见应用：

1. 生成数字序列

递归CTE可以轻松生成连续数字序列，无需物理表：

WITH Series AS ( -- 锚点查询: 起始值 SELECT 1 AS MyNumber UNION ALL -- 递归查询: 自增 SELECT MyNumber + 1 FROM Series WHERE MyNumber < 20 ) SELECT * FROM Series;

如需生成超过100的序列，需使用OPTION (MAXRECURSION N)选项：

WITH Series AS ( SELECT 1 AS MyNumber UNION ALL SELECT MyNumber + 1 FROM Series WHERE MyNumber < 1000 ) SELECT * FROM Series OPTION (MAXRECURSION 5000); -- 增加递归限制

2. 构建组织层次结构

递归CTE非常适合处理员工层级关系。以下示例基于SalesDB数据库的员工表构建组织树：

SalesDB数据模型展示了Orders表与Customers、Products和Employees表的关系，适合使用CTE进行多表关联查询

WITH CTE_Emp_Hierarchy AS ( -- 锚点查询: 顶级员工(无经理) SELECT EmployeeID, FirstName, ManagerID, 1 AS Level FROM Sales.Employees WHERE ManagerID IS NULL UNION ALL -- 递归查询: 获取下属并增加层级 SELECT e.EmployeeID, e.FirstName, e.ManagerID, Level + 1 FROM Sales.Employees AS e INNER JOIN CTE_Emp_Hierarchy AS ceh ON e.ManagerID = ceh.EmployeeID ) SELECT * FROM CTE_Emp_Hierarchy;

CTE性能优化技巧

使用CTE时，需注意以下性能优化建议：

合理使用索引：确保CTE中引用的列有适当索引，特别是过滤和连接条件
限制递归深度：避免无限递归，必要时使用MAXRECURSION控制
简化逻辑：复杂CTE可拆分为多个简单CTE，提高效率和可读性
避免过度使用：简单查询无需使用CTE，过度使用会增加开销

如何开始使用SQL CTE？

要开始使用SQL Ultimate Course中的CTE功能，可按以下步骤操作：

克隆项目仓库：git clone https://gitcode.com/gh_mirrors/sq/sql-ultimate-course
查看CTE示例脚本：scripts/19_Common_Table_Expressions_CTE.sql
参考数据库初始化脚本创建测试环境：
- MySQL: datasets/mysql/init-mysql-salesdb.sql
- PostgreSQL: datasets/postgres/init-postgres-salesdb.sql
- SQL Server: datasets/sql-server/init-sqlserver-salesdb.sql