概念:Common Table Expression,简称CTE,中文可以叫做,通用表表达式.
用处:处理以前版本中SQL不好现实,不好理解,复杂的查询问题.比如:分页,递归查询...
基本用法:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WITH <name of your CTE>(<column names>)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
AS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
<actual query>
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT * FROM <name of your CTE>
示例一(基本用法):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
with MyCTE(ID, Name)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select EmployeeID as ID, FirstName + ' ' + LastName as Name
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from HumanResources.vEmployee
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from MyCTE
示例二(分页):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
with MyCTE(ID, Name, RowID)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select EmployeeID as ID, FirstName + ' ' + LastName as Name,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Row_Number() over (order by EmployeeID) as RowID
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from HumanResources.vEmployee
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from MyCTE where RowID between 1 and 10
示例三(关联CTE):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
with OrderCountCTE(SalesPersonID, OrderCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select SalesPersonID, count(1)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from Sales.SalesOrderHeader
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
where SalesPersonID is not null
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
group by SalesPersonID
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select sp.SalesPersonID, sp.SalesYTD, cte.OrderCount
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from OrderCountCTE cte inner join Sales.SalesPerson sp
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
on cte.SalesPersonID = sp.SalesPersonID order by 3
示例四(使用CTE的删除):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE TABLE Products (
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Product_ID int NOT NULL,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Product_Name varchar (25),
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Price money NULL,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (Product_ID)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (1, 'Widgets', 25)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (2, 'Gadgets', 50)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (3, 'Thingies', 75)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (4, 'Whoozits', 90)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (5, 'Whatzits', 5)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (6, 'Gizmos', 15)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (7, 'Widgets', 24)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (8, 'Gizmos', 36)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (9, 'Gizmos', 36)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--==================Delete duplicate products=============================
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
with DuplicateProdCTE
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(select Min(Product_ID) as Product_ID, Product_Name
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from Products
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
group by Product_Name
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
having count(1) >1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
delete Products from Products p join DuplicateProdCTE cte
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
on cte.Product_Name = p.Product_Name and p.Product_ID > cte.Product_ID
示例五(递归查询):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, ReportsTo int)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--insert some data, build a reporting tree
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Malek', 4, 2)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
with MyCTE
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
as
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
( select Employee_ID, Employee_NM, -1 as ReportsTo, 0 as SubLevel
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from Employee_Tree where ReportsTo is null --root node
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
union all
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel +1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
) --select * from MyCTE
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select MyCTE.Employee_NM as emp , MyCTE.SubLevel, e.Employee_NM as boss
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
from MyCTE left join Employee_Tree e on MyCTE.ReportsTo = e.Employee_ID
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--OPTION(MAXRECURSION 3) --error
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--OPTION(MAXRECURSION 4) --ok
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
where SubLevel < 4
注意:OPTION(MAXRECURSION 4)用来设置递归时查找的层数,默认是100,如果超过默认或指定的,则会报错.通常我们用一个层数列来过滤指定的层.