在SQL Server用CTE进行子树节点的查询

感谢各位朋友，我记录在这博客里，以便查询

http://topic.youkuaiyun.com/u/20101215/12/bf221d61-5a46-42a0-9333-f2159b8c581f.html?seed=407555261&r=70600020#r_70600020

 

表结构和数据如下：

ID PID Name
1 null name1
2 null name2
3 null name3
4 1 name4
5 2 name5
6 1 name6
7 2 name7
8 3 name8
9 4 name9

我想写一个查询，查询条件是PID是1，2的子树，结果如下：
ID PID Name
4 1 name4
5 2 name5
6 1 name6
7 2 name7
9 4 name9

如何写这个查询？

 

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO

---->建表
create table [TB]([ID] int,[PID] int,[Name] varchar(5))
insert [TB]
select 1,null,'name1' union all
select 2,null,'name2' union all
select 3,null,'name3' union all
select 4,1,'name4' union all
select 5,2,'name5' union all
select 6,1,'name6' union all
select 7,2,'name7' union all
select 8,3,'name8' union all
select 9,4,'name9'
GO



--> 查询结果

SELECT * FROM [TB]

with aa as
(
SELECT ID,PID,Name FROM [TB] where PID in (1,2)
union all
SELECT TB.* FROM aa join TB on TB.PID=aa.ID
and TB.PID is not null
)
SELECT * FROM aa

 

 

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