使用公用表表达式(CTE)

 通用表表达式（CTEs）是SQL Server 2005的一项新功能。它们类似于alias（如在SELECT T1.* FROM MyTable T1中），不过功能更为强大。本质上，CTE是一个临时结果集，它仅仅存在于它发生的语句中。您可以在SELECT、INSERT、DELETE、 UPDATE或CTEATE VIEW语句中建立一个CTE。CTE类似于派生表，但拥有几项优点。

CTE的优点

与派生表不同，CTE能够引用自己本身。如果您不必存储视图，您可以用一个CTE来代替它。在一个语句中，您还可以多次引用CTE。应用CTE，您可以通过一个派生栏对结果进行分组。
您可以将查询区域分割成可读的“块”，然后用这些块建立一个复杂的查询。执行递归查询是CTE最重要也是最强大的功能。

建立CTE

CTE通过关键字WITH建立，其模板为：

WITH  CTE_name [  (column_name [,...n ]  ) ]
AS
( CTE_query_specification )

以下为多个示例的使用:

USE  Northwind
-- 结果列别名
WITH  c  AS  
(
SELECT   YEAR (orderdate)  AS  orderyear,customerid
FROM  Orders
)

SELECT  orderyear, COUNT ( DISTINCT  customerid) AS  numCusts  FROM  c  GROUP   BY  orderyear
-- 使用参数
DECLARE   @empid   AS   INT  
SET   @empid = 3
WITH  c  AS  
(
SELECT   YEAR (orderdate)  AS  orderyear,customerid
FROM  Orders
WHERE  employeeid = @empid
)
SELECT  orderyear, COUNT ( DISTINCT  customerid) AS  numCusts  FROM  c  GROUP   BY  orderyear


-- 多CTE.不同于派生表,CTE不能被直接嵌套.即,你不能在一个CTE内定义另一个CTE.但是,你可以用一个WITH语句定义多个CTE.
WITH  c1  AS  
(
SELECT   YEAR (orderdate)  AS  orderyear,customerid
FROM  Orders
),
c2  AS  
(
SELECT  orderyear, COUNT ( DISTINCT  customerid) AS  numcusts  FROM  c1  GROUP   BY  orderyear
)

SELECT  orderyear,numcusts  FROM  c2  WHERE  numcusts > 70


-- 多引用: 相对于派生表,CTE的一个优势是你可以在外部查询中多次引用同一个CTE名称.
WITH  YearlyCount  AS  
(
SELECT   YEAR (orderdate)  AS  orderyear, COUNT ( DISTINCT  customerid) AS  numcusts  FROM  orders  GROUP   BY   YEAR (orderdate)
)

SELECT  cur.orderyear,cur.numcusts,prv.orderyear,prv.numcusts,Cur.numcusts - prv.numcusts
FROM  yearlyCount Cur  LEFT   JOIN  YearlyCount Prv
ON  cur.Orderyear = Prv.orderyear + 1

-- 修改数据.可能通过CTE修改数据
-- 建立测试表
IF   OBJECT_ID ( ' CustomersDups ' ) IS   NOT   NULL
DROP   TABLE  CustomersDups

WITH  CrossCustomers  AS  
(
SELECT   1   AS  c,c1. *   FROM  customers  AS  c1,customers  AS  c2
)
SELECT  ROW_NUMBER() OVER ( ORDER   BY  c) AS  keycol,CustomerID,CompanyName,ContactName,ContactTitle,Address,City,
Region,PostalCode,Country,Phone,Fax    
INTO  customerdups 
FROM  CrossCustomers 

SELECT   *   FROM  customerdups

CREATE   UNIQUE   INDEX  index_idx_customerid_keycol
ON  customerdups (customerid,keycol)

-- 以下删除重复的数据.
WITH  JustDups  AS  
(
--此查询语句不能得到对应的重复数据 SELECT   *   FROM  customerdups  AS  c1  WHERE  keycol < ( SELECT   MAX (keycol)  FROM  customerdups  AS  c2  WHERE  c2.customerid = c1.customerid)

    select * from customerdups as c1 where keycol in (select max(keycol) from customerdups as c2 group by col1.col2...)
)
DELETE   FROM  justdups

-- 容器对象,CTE可以用于诸如视图或内联UDF这样的容器中.这种能力允许实现封装.要熟练掌握.
-- 视图
CREATE   VIEW  dbo.vYearCnt
AS  
WITH  YearCnt  AS  
(
SELECT   YEAR (orderdate)  AS  orderyear, COUNT ( DISTINCT  customerid) AS  numCusts  FROM  Orders  GROUP   BY   YEAR (orderdate)
)
SELECT   *   FROM  YearCnt
GO  

SELECT   *   FROM  vYearCnt

-- UDF,可传递一个输入参数
CREATE   FUNCTION  dbo.ufn_EmpYearCnt( @EmpID   AS   INT )  RETURNS   TABLE    
AS    
RETURN    
   WITH  EmpYearCnt  AS    
  (  
     SELECT   YEAR (OrderDate)  AS  OrderYear,  
      COUNT ( DISTINCT  CustomerID)  AS  NumCusts  
      FROM  dbo.Orders  
      WHERE  EmployeeID = @EmpID   
      GROUP   BY   YEAR (OrderDate)  
  )  
   SELECT   *   FROM  EmpYearCnt;

SELECT   *   FROM  ufn_EmpYearCnt( 2 )

-- 递归CTE，是SQL 2005中最重要的TSQL增强之一。通过纯基于集合的查询实现了递归查询。
/**/ /*下面用一个示例来描述递归CTE。给一个参数，要求你根据由EmployeeID和ReportsTo属性维护的层次关系，返回输入员工和该员工所有级别的下属。要为每个员工返回的属性包括：employeeID、ReportsTo、FirstName和LastName
*/
-- 先创建覆盖索引，以忧化该任务。
CREATE   UNIQUE   INDEX  idx_mgr_emp_ifname_ilname
ON  employee(reportsTo,EmployeeID)include(FirstName,LastName)
-- 该索引允许通过使用一次查找和一次局部扫描提取每位经理的直接下属。注意索引中包含列FirstName和LastName以实现覆盖。下面是递归CTE。
WITH  EmpsCTE  AS  
(
SELECT  employeeID,ReportsTo,Firstname,LastName
FROM  Employees
WHERE  employeeID = 5
UNION   ALL
SELECT  Emp.EmployeeID,Emp.ReportsTo,Emp.FirstName,Emp.LastName
FROM  EmpsCTE  AS  Mgr  JOIN  employees Emp
ON  emp.ReportsTo = mgr.employeeid
)
-- 查看员工5和它的下属
SELECT   *   FROM  EmpsCTE

/**/ /*递归CTE最少包含两个查询（也称为成员）。CTE主体中的第一个查询被称为定位点成员（Anchor Member）。定位点成员只是一个返回有效表的查询，用于递归的基础或定位点。在以上的示例中，定位点成员直接返回输入根员工（员工5）所在的行。CTE主体中的第二个查询递归成员（Recursive Member）。使该查询成为递归成员的是对CTE名称（EmpsCTE）的递归引用。注意该引用不同于在外部查询中对CTE名称的引用。外部查询中的引用得到由该CTE返回的结果集，不包含递归。而内部引用是在该CTE的结果表确定之前使用的，它是触发递归的关键元素。*/

DROP   INDEX  employees.idx_mgr_emp_ifname_ilname

 限制：不能在一个语句中建立两个CTE。