T-SQL Enhancement in SQL Server 2005 - Part I

核心提示： WITH CTE_SalesOrder_CountAS(SELECT CustomerID, Count(*) As OrderCountFROM Sales.SalesOrderHeaderGROUP BY CustomerID)SELECT Sales.Customer.Custome

WITH CTE_SalesOrder_Count
AS
(
　　SELECT CustomerID, Count(*) As OrderCount
　　FROM Sales.SalesOrderHeader
　　GROUP BY CustomerID
)
SELECT Sales.Customer.CustomerID, AccountNumber,OrderCount
FROM Sales.Customer INNER JOIN CTE_SalesOrder_Count
ON CTE_SalesOrder_Count.CustomerID = Sales.Customer.CustomerID

2．　　　　　使用CTE代替自连接，以便更易于理解。

假设我们有一个Product表用于存储每个Product的信息，每个Product有一个唯一标识Product_ID和一个不唯一的Product_Name。由于不同的Product可能重名，倘若我们有这样的一个需求：需要将重名的记录（除了具有最小ID的那个）删除，从而保证其名称的唯一性。我们来看看如何保这些需要上出的记录筛选出来。Product表的记录如下，ID为1和4的两条记录重名，现在我们的目的是把ID为4的记录筛选出来。

在不考虑CTE的情况下，我们通过下面的SQL实现这个功能，这个SQL采用了自连接。虽然SQL看起来很简洁，但是相信有一些人第一次看到这样一个SQL，不能立即理解。

CTE，通过下面一段SQL来实现，虽然代码多了点，但是从语义上看要易于理解一点：首先把重名的选出来，在和Product作一次连接。

WITH CTE_PRODUCT(PRODUCT_ID,PRODUCT_NAME)
AS
(
　　SELECT MIN(PRODUCT_ID) AS PRODUCT_ID,PRODUCT_NAME
　　FROM dbo.PRODUCT
　　GROUP BY PRODUCT_NAME
　　HAVING COUNT(*)>1
)
SELECT dbo.PRODUCT.PRODUCT_ID, dbo.PRODUCT.PRODUCT_NAME
FROM dbo.PRODUCT
INNER JOIN CTE_PRODUCT
ON CTE_PRODUCT.PRODUCT_NAME = dbo.PRODUCT.PRODUCT_NAME
AND dbo.PRODUCT.PRODUCT_ID > CTE_PRODUCT.PRODUCT_ID

3．　　　　　用于具有层次结构记录的递归查询

比如一个公司的员工体系就是一个包含上下级关系的具有层次化的树形结构。假设我们有如下一个EMPLOYEE表，通过REPORT_TO体现每个员工的上下级关系（假设Empoyee_Name具有唯一性）。

我们现在的需求是：列出员工A的所有下级。

为了实现这样的一个功能，我们需要以一种特殊的结构来创建CTE：

CTE中主体部分由两个SELECT语句组成，我们把第一个叫做Anchor Member（AM），AM不会递归，只会执行一次，本例中筛选出了级别最高的A；另一个SELECT语句叫做Recursive Member（RM），RM通过CTE本身和EMPLOYEE表建立连接，所以RM会采用递归的方式执行。