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