SQL CTE

### SQL Common Table Expressions (CTE) 用法及示例 在SQL中，Common Table Expressions（CTE）是一种临时的结果集，它可以在单个SELECT、INSERT、UPDATE或DELETE语句中引用。CTE通过WITH关键字定义，并且可以简化复杂的查询逻辑[^1]。CTE不仅可以用于SELECT语句，还可以与数据修改语句（如INSERT、UPDATE、DELETE或MERGE）结合使用[^2]。 #### 定义 CTE 的基本语法 ```sql WITH CTE_Alias AS ( SELECT column1, column2, ... FROM table_name WHERE condition ) SELECT * FROM CTE_Alias; ``` - `WITH` 关键字后面跟着一个别名（`CTE_Alias`），用于标识CTE。 - 在括号内定义子查询，该子查询生成的结果集将作为CTE的内容。 - 最后，在主查询中引用CTE的别名。 #### 示例 1：简单的 CTE 查询 以下是一个简单的CTE示例，展示如何从表中选择数据并计算下一季度的销售额： ```sql WITH QuarterlySales AS ( SELECT [Year], [Quarter], Sales FROM dbo.ProductSales ) SELECT [Year], [Quarter], Sales, LEAD(Sales, 1, 0) OVER(ORDER BY [Year], [Quarter] ASC) AS [NextQuarterSales] FROM QuarterlySales; ``` 在这个例子中，CTE (`QuarterlySales`) 用于封装基础数据的选择逻辑，而主查询则利用窗口函数 `LEAD` 来计算下一季度的销售额[^3]。 #### 示例 2：递归 CTE CTE 还支持递归查询，这对于处理层级结构的数据非常有用。例如，假设我们有一个员工表，其中包含员工及其直接上级的信息，我们可以使用递归来查询某个员工的所有下属。 ```sql WITH EmployeeHierarchy AS ( -- 锚点成员：选择初始记录 SELECT EmployeeID, Name, ManagerID, 1 AS Level FROM Employees WHERE EmployeeID = 1 -- 假设我们要查找EmployeeID为1的员工的所有下属 UNION ALL -- 递归成员：查找下一级别的记录 SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1 FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchy; ``` 在此示例中，递归CTE被用来构建一个组织层次结构[^4]。递归部分通过 `UNION ALL` 将当前级别的记录与下一级别的记录合并，直到没有更多的记录可加入为止。 #### 示例