公用表表达式（CTE）是微软在SQL Server 2005中首次引入的，其类似于一个非持久性的视图。这是您在查询中定义的一个临时命名结果集，且将会在查询的FROM子句中使用。每一个CTE只定义一次（但可以在生命期内被费用多次），且生命期持续到查询结束。您可以使用CTEs来执行递归操作。下面是创建一个CTE的语法:
WITH <name of your CTE>(<column names>)
AS
(
<actual query>
)
SELECT * FROM <name of your CTE>
注意：本文章中使用AdventureWorks2008作为示例数据库。请自行从codeplex下载此数据库并安装。
使用AdventureWorks2008示例数据库的一个简单CTE的例子如下：
USE AdventureWorks2008
GO
WITH AllMRContacts
AS
(
SELECT * FROM Person.Person WHERE Title = 'Mr.'
)
SELECT LastName + ', ' + FirstName AS Contact
FROM AllMRContacts
ORDER BY LastName
此例子的执行结果为：
Contact
-------------------
Abbas, Syed
Achong, Gustavo
Adams, Jay
Adams, Ben
Adina, Ronald
Agcaoili, Samuel
下面的例子使用CTE统计AdventureWorks2008示例库中销售人员的销售订单数，然后此CTE和SalesPerson表进行内连接，返回更多销售人员的信息。此示例演示了如何在查询中连接CTE。不用CTE你也可以达到相同的目的，但想像一下你不得不费力创建临时表或最终要告诫的视图并再连接回去的过程。现在你可以使用CTE，并将聚合的复杂性限制在CTE中，因此简化了代码，如下：
WITH OrderCountCTE(SalesPersonID, OrderCount)
AS
(
SELECT SalesPersonID, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
SELECT
sp.BusinessEntityID,
FirstName + ' ' + LastName as SalesPerson,
oc.OrderCount,
sp.SalesYTD
FROM Sales.vSalesPerson AS sp
INNER JOIN OrderCountCTE AS oc ON oc.SalesPersonID = sp.BusinessEntityID
ORDER BY oc.OrderCount DESC
代码执行结果就像如下所示：
BusinessEntityID SalesPerson OrderCount SalesYTD
----------------- ------------------------ ----------- --------------
277 Jillian Carson 473 3857163.6332
275 Michael Blythe 450 4557045.0459
279 Tsvi Reiter 429 2811012.7151
276 Linda Mitchell 418 5200475.2313
289 Jae Pak 348 5015682.3752
282 José Saraiva 271 3189356.2465
281 Shu Ito 242 3018725.4858
278 Garrett Vargas 234 1764938.9859
283 David Campbell 189 3587378.4257
290 Ranjit Varkey Chudukatil 175 3827950.238
284 Tete Mensa-Annan 140 1931620.1835
288 Rachel Valdez 130 2241204.0424
286 Lynn Tsoflias 109 1758385.926
280 Pamela Ansman-Wolfe 95 0.00
274 Stephen Jiang 48 677558.4653
287 Amy Alberts 39 636440.251
285 Syed Abbas 16 219088.8836
CTEs也可以用来消除自连接。请看下面的例子，这个例子中我们创建一个Products表，将插入一些重复的数据：
CREATE TABLE Products
(ProductID int NOT NULL,
ProductName varchar(25),
Price money NULL,
CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (ProductID)
)
GO
INSERT INTO Products VALUES (1, 'Widgets', 25)
INSERT INTO Products VALUES (2, 'Gadgets', 50)
INSERT INTO Products VALUES (3, 'Thingies', 75)
INSERT INTO Products VALUES (4, 'Whoozits', 90)
INSERT INTO Products VALUES (5, 'Whatzits', 5)
INSERT INTO Products VALUES (6, 'Gizmos', 15)
INSERT INTO Products VALUES (7, 'Widgets', 24)
INSERT INTO Products VALUES (8, 'Gizmos', 36)
INSERT INTO Products VALUES (9, 'Gizmos', 36)
数据库的一个常见问题是相同的产品名却有着不同的产品ID。如果你执行一个查找重复数据的查询，这个查询将返回所有的数据，包括重复的和正常的。这将增加自动删除重复数据的困难。如果你想查找ProductName的重复数据且不包括在表出现的第一个值，你可以使用自连接，见如下代码：
SELECT * FROM Products WHERE ProductID NOT IN
(SELECT MIN(ProductID) FROM Products AS P
WHERE Products.ProductName = P.ProductName)
代码执行结果如下：
ProductID ProductName Price
--------- ----------- -----------
8 Gizmos 36.00
9 Gizmos 36.00
7 Widgets 24.00
你可以使用CTE改写以上的代码，以消除看上支比较费解的自连接代码。这种技术相对自连接并不能得到性能上的好处，只是代码维护上更好一些。改写后的代码如下，注意我们将CTE与Products进行了连接：
WITH MinProductRecords AS
(
SELECT MIN(ProductID) AS ProductID, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
SELECT P.*
FROM Products AS P
INNER JOIN MinProductRecords AS MP
ON P.ProductName = MP.ProductName AND P.ProductID > MP.ProductID
使用上面的CTE检查了重复数据后，你可能想删除它们。你可能也想使用重复数据的ProductID值更新那些相关表的外键。你可以通过改写上面的CTE–将SELECE *改为DELETE–达到删除重复数据的效果：
WITH MinProductRecords AS
(
SELECT MIN(ProductID) AS ProductID, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE Products
FROM Products AS P
INNER JOIN MinProductRecords AS MP
ON P.ProductName = MP.ProductName AND P.ProductID > MP.ProductID
(未完待续...)
SQL Server
