CTE 通用表表达式

CTE 通用表表达式

概念：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)用来设置递归时查找的层数，默认是，如果超过默认或指定的，则会报错．通常我们用一个层数列来过滤指定的层．