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-courseSQL 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.sqlPostgreSQL: datasets/postgres/init-postgres-salesdb.sqlSQL Server: datasets/sql-server/init-sqlserver-salesdb.sql总结CTE是SQL查询的强大工具能够将复杂逻辑分解为可读的模块化组件。通过SQL Ultimate Course中的实际案例我们展示了如何利用CTE简化数据分析、生成序列和处理层次结构数据。无论是数据分析人员还是开发人员掌握CTE都将大大提升SQL查询的质量和效率。深入学习可参考课程文档docs/09_Advanced_SQL_Techniques.pdf其中包含更多高级CTE应用和性能优化策略。【免费下载链接】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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考