概念：Common Table Expression，簡稱CTE，中文可以叫做，通用表表達式．

用處：處理以前版本中SQL不好現實，不好理解，復雜的查詢問題．比如：分頁，遞歸查詢．．．

基本用法：

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

注意： OPTION(MAXRECURSION 4) 用來設置遞歸時查找的層數，默認是100，如果超過默認或指定的，則會報錯．通常我們用一個層數列來過濾指定的層．