SQL Server 中的 CTE
4.87/5 (34投票s)
SQL Server 中的 CTE
引言
对于任何操作临时结果集的操作，SQL Server 提供了哪些选项？我们有临时表、表变量、表值参数，当然还有表值函数。但是，随着 SQL Server 2005 及更高版本的出现，为了方便程序员，增加了一个非常强大的功能：公用表表达式 (CTE)。它简化了复杂的查询，最重要的是，它使您能够递归，我说的是递归值。是的，就像任何编程语言 C#、C++ 等一样。令人惊叹，不是吗！让我们深入研究这个强大的功能，了解它是如何工作的以及它提供了哪些功能。
CTE 又是从底层定义派生的临时结果集。关于 CTE 的语法，请参阅 MSDN。
一个简单的说明：CTE 作为派生表
我们的数据库中有一个简单的表 Products。
Select * from PRODUCTS
ProductID ProductDesc ManufacturingDate ExpiryDate IsSalable Price
---------------------------------------------------------------------------------------
1 Biscuits 2011-09-01 00:00:00.000 2012-09-01 00:00:00.000 1 20.00
2 Butter 2010-09-01 00:00:00.000 2011-09-01 00:00:00.000 1 30.00
3 Milk 2011-10-01 00:00:00.000 2011-11-01 00:00:00.000 1 46.00
我们创建了一个简单的 ProductsCTE，用于显示所有Price大于20.00的Products。这里 CTE 执行了作为简单派生表的操作。
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
FROM PRODUCTS
WHERE Price>20.00
)
SELECT * FROM ProductsCTE
ProdName Price
-------------------------------------------------- ---------------------
Butter 30.00
Milk 46.00
(2 row(s) affected)
需要提及的一个重要点是 CTE 定义后的 SELECT 语句，任何操作 SELECT、INSERT、UPDATE、DELETE 或 Merge 都可以立即在 CTE 之后执行，并且 CTE 仅持续于单个此类操作。当我说这些时，我的意思是...
这意味着以下代码是错误的
DECLARE @T INT,@I INT
SET @T = 10
SET @I = 20
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
FROM PRODUCTS
WHERE Price>20.00
)
SELECT @T+@I
SELECT * FROM ProductsCTE
执行代码后，会产生以下错误。这意味着我需要在 CTE 定义完成后立即执行 ProductsCTE SELECT。
Msg 422, Level 16, State 4, Line 10
Common table expression defined but not used.
为了纠正这一点... 我将恢复顺序为
DECLARE @T INT,@I INT
SET @T = 10
SET @I = 20
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
FROM PRODUCTS
WHERE Price>20.00
)
SELECT * FROM ProductsCTE
SELECT @T+@I
ProdName Price
-------------------------------------------------- ---------------------
Butter 30.00
Milk 46.00
(2 row(s) affected)
-----------
30
(1 row(s) affected)
让我们执行一个 UPDATE
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
FROM PRODUCTS
WHERE Price>20.00
)
UPDATE ProductsCTE SET Price=50 WHERE ProdName='Milk'
SELECT * FROM ProductsCTE
-------------------
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 7
Invalid object name 'ProductsCTE'.
Milk 的价格已成功更新为 50，但下一组 select 语句不起作用。请始终记住，您可以一次获取一个结果集，并在 CTE 定义后立即获取。例如：
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
FROM PRODUCTS
WHERE Price>20.00
)
SELECT * FROM ProductsCTE
UNION
SELECT 'Bread' AS ProdName,MIN(Price) AS PRICE from ProductsCTE
ProdName Price
-------------------------------------------------- ---------------------
Bread 30.00
Butter 30.00
Milk 50.00
(3 row(s) affected)
Calling Multiple CTEs
我们可以从单个查询中调用多个 CTE。让我们来看一个示例。我们有两个表
Select * from Student
Select * from Teacher
让我们实现一个 CTE 来获取所有学生respective class teachers。
;WITH StudCTE(RollNo,StudentName,TeacherID)
AS
(
SELECT ID,Name,TID FROM Student
)
,TeacherCTE(TID,TeacherName)
AS
(
SELECT ID,Name FROM Teacher
)
SELECT RollNo,StudentName,TeacherName
FROM StudCTE SC
INNER JOIN
TeacherCTE TC
ON SC.TeacherID=TC.TID
我们从单个 SELECT 语句中调用了两个 CTE，并基于内连接返回了学生-教师信息。这是一个简单的示例，展示了如何执行多个 CTE。
复杂场景
那么 CTE 的大用处是什么呢？用处在于当您需要一些复杂的查询或操作时，相信我，没有什么比 CTE 更好用了。让我们来看看一个最常见遇到的复杂问题... 重复项。我们有一个示例表 (EMP) 用于演示。
Select * from EMP
EID ENAME DEPT
----------- -------------------- ----------
1 Sara IT
2 Rick HR
3 Ted IT
4 Sheldon Accounts
5 Sara IT
(5 row(s) affected)
为了删除表中重复的员工，即“Sara”，我们创建一个 CTE
;WITH EliminateDup(Eid,Name,Dept,RowID) AS
(
SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID
FROM EMP
)
DELETE FROM EliminateDup WHERE RID>1
The query below creates a temporary result set as :
SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID
FROM EMP
Eid Ename Dept RowID
----------- -------------------- ---------- --------------------
2 Rick HR 1
1 Sara IT 1
5 Sara IT 2
4 Sheldon Accounts 1
3 Ted IT 1
(5 row(s) affected)
之后，我们使用 DELETE 删除重复项。很简单，不是吗？
递归
下一个也是最重要的功能是递归。
通过 UNION ALL，我们可以使 CTE 递归，从而形成最终结果。有一个锚定成员和一个递归成员，它们可能有一个终止条件，也可能没有。让我们来看一个例子。
假设我们有一个逗号分隔的 string，我们希望从 string 中提取每个单词。
让我们考虑 string 为 ‘Where,there,is,a,will,there,is,a,way’。
DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(
SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select SUBSTRING(@T,Start,[End]-Start)from MyCTE;
让我们理解一下我们在这里做了什么... 我们有一个锚定，形式为 SELECT 1,CHARINDEX(',',@T,1)。
第一次迭代，锚定返回 1,6（该值是单词 ‘Where,’ 之后第一个逗号的 CHARINDEX）的值，分别用于 Start 和 [End] 列。
接下来，递归代码返回 [End]+1=7 作为 Start，以及 12 作为 [End]（通过 CHARINDEX(',',@T,[End]+1 计算得出，即 7），这段代码会一直递归，直到满足终止条件 [End]<LEN(@T)，即 37。
UNION ALL 操作符将所有 Start 和 [End] 连接起来，为了清晰起见，让我们再看一下这些值。
DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(
SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE;
希望这能让事情更清楚。通过 CTE，我们可以实现与 C# 或 C++ 相同的可编程性，例如生成斐波那契数列、特定的 string 模式等。递归在需要报告层次结构时尤其有用，我们稍后会看到。目前，让我们看看递归选项。
如果我们只想获取 string 中的前两个值呢？
DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(
SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE
OPTION (MAXRECURSION 1);
OPTION MAXRECURSION 命令使代码仅递归一次，并在发生时终止。自解释消息会出现，结果窗格中返回的值为：
- 初始锚定值
- 第一次递归值
MAXRECURSION 的值可以在 0 到 32,767 之间。32,767 没问题，但 0 会返回什么？0 允许无限递归，因此如果递归语句没有终止条件，程序将无限循环。为了亲身体验，请尝试以下代码？
;WITH MyCTE(Val) AS(
SELECT 1 AS Val
UNION ALL
SELECT Val=(Val+1) FROM MyCTE
)
Select Val from MyCTE
OPTION (MAXRECURSION 0);
获取层次结构
在我们结束之前，让我们来看最后一个示例，获取一个组织完整的层次结构。在这种情况下，CTE 在简洁性和所需的 LOC（代码行数）方面可以超越任何复杂的代码来得出结果。
我们有一个如下的 Org 表：
Select * from Org
为了获取自底向上的层次结构，我们传递 eid 并获取相关员工的完整层次结构。例如，对于 Andy 的组织层次结构，我们传递 @T = 6（他的 Eid）。
DECLARE @T INT = 6
;WITH OrgCTE(Eid,Employee,SupervisorID,ReportsTo)AS
(
SELECT @T,O.Name,O2.EID,O2.Name FROM Org O
INNER JOIN Org O2 ON O.SupervisorID=O2.EID
AND O.EID=@T
UNION ALL
SELECT OC.SupervisorID,OC.ReportsTo,O2.EID,O2.Name
FROM OrgCTE OC
INNER JOIN
Org O
ON OC.SupervisorID=O.EID
INNER JOIN
Org O2
ON O.SupervisorID=O2.EID
)
SELECT * FROM OrgCTE
因此，我们成功地获得了 Andy 的层次结构。
同样，对于自顶向下的层次结构，我们可以实现以下 CTE，该 CTE 返回一个指示自顶向下组织图的级别。
;WITH OrgCTE(Eid,SupervisorID,Employee,[Role],[Level])AS
(
SELECT EID,SupervisorID,Name,[Role],0 FROM
Org WHERE SupervisorID=0
UNION ALL
SELECT O.EID,O.SupervisorID,O.Name,O.[Role],[Level]+1
FROM Org O
INNER JOIN OrgCTE OC
ON O.SupervisorID=OC.Eid
)
SELECT * FROM OrgCTE
SQL 程序员发现 CTE 非常有用，得益于此功能，编程生活中的复杂性已大大简化。我希望我在本文中能充分说明 CTE 的优点。
历史
- 2011 年 10 月 31 日：初始版本