步步为营：SQL的公共表达式（CTE）

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CTE 可用于：

       处理以前版本中SQL不好现实，不好理解，复杂的查询问题．比如：分页，递归查询．．．

          创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询 。

• 在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
• 启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
• 在同一语句中多次引用生成的表。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

基本用法：

 WITH < name of your CTE > ( < column names > )
 AS 
 (
 < actual query > 
 )
 
 SELECT * FROM < name of your CTE > 

 

示例一（基本用法）：

 with MyCTE(ID, Name)
 as 
 (
      select EmployeeID as ID, FirstName + ' ' + LastName as Name
      from      HumanResources.vEmployee
 )
 select * from MyCTE

示例二（分页）：

 with MyCTE(ID, Name, RowID)
 as 
 (
      select EmployeeID as ID, FirstName + ' ' + LastName as Name,
              Row_Number() over ( order by EmployeeID) as RowID
      from      HumanResources.vEmployee
 )
 select * from MyCTE where RowID between 1 and 10

 

示例三（关联CTE）:

 with OrderCountCTE(SalesPersonID, OrderCount)
 as
 (
      select    SalesPersonID, count ( 1) 
      from     Sales.SalesOrderHeader
      where      SalesPersonID is not null
      group by SalesPersonID
 )
 select sp.SalesPersonID, sp.SalesYTD, cte.OrderCount
 from OrderCountCTE cte inner join   Sales.SalesPerson sp
 on cte.SalesPersonID = sp.SalesPersonID order by 3

示例四（使用CTE的删除）：

 CREATE TABLE Products (
    Product_ID int NOT NULL ,
    Product_Name varchar ( 25 ),
    Price money NULL ,
 CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (Product_ID)
 )
 GO 
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 1 , ' Widgets ' , 25 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 2 , ' Gadgets ' , 50 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 3 , ' Thingies ' , 75 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 4 , ' Whoozits ' , 90 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 5 , ' Whatzits ' , 5 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 6 , ' Gizmos ' , 15 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 7 , ' Widgets ' , 24 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 8 , ' Gizmos ' , 36 )
 INSERT INTO Products (Product_ID, Product_Name, Price) VALUES ( 9 , ' Gizmos ' , 36 )
 GO 
 
 -- ==================Delete duplicate products============================= 
 with DuplicateProdCTE
 as 
 ( select Min (Product_ID) as Product_ID, Product_Name
       from      Products
       group by Product_Name
       having count ( 1 ) > 1 
 )
 delete Products from Products p join DuplicateProdCTE cte 
 on cte.Product_Name = p.Product_Name and p.Product_ID > cte.Product_ID

示例五（递归查询）：

 CREATE TABLE Employee_Tree (Employee_NM nvarchar ( 50 ), Employee_ID int PRIMARY KEY , ReportsTo int )
 -- insert some data, build a reporting tree 
 INSERT INTO Employee_Tree VALUES ( ' Richard ' , 1 , NULL )
 INSERT INTO Employee_Tree VALUES ( ' Stephen ' , 2 , 1 )
 INSERT INTO Employee_Tree VALUES ( ' Clemens ' , 3 , 2 )
 INSERT INTO Employee_Tree VALUES ( ' Malek ' , 4 , 2 )
 INSERT INTO Employee_Tree VALUES ( ' Goksin ' , 5 , 4 )
 INSERT INTO Employee_Tree VALUES ( ' Kimberly ' , 6 , 1 )
 INSERT INTO Employee_Tree VALUES ( ' Ramesh ' , 7 , 5 )
 --
 with MyCTE
 as 
 (     select Employee_ID, Employee_NM, - 1 as ReportsTo, 0 as SubLevel
      from Employee_Tree where ReportsTo is null -- root node 
      union all 
      select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel + 1 
      from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID
 
 ) -- select * from MyCTE 
 select MyCTE.Employee_NM as emp , MyCTE.SubLevel, e.Employee_NM as boss 
 from MyCTE left join Employee_Tree e on MyCTE.ReportsTo = e.Employee_ID
 -- OPTION(MAXRECURSION 3) --error 
 -- OPTION(MAXRECURSION 4) --ok 
 where SubLevel < 4