问一个关于递归搜索（子树）的问题

[Dominic]

原本想搜索一下先，但发现csdn的搜索我不会用了，汗！ ^_^

表结构和数据如下：

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

如何写这个查询？

[王向飞]

2000用函数
递归函数 大乌龟有

[王向飞]

/*
标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go

--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go

--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

（所影响的行数为 10 行）
*/

--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
002  001  广州市
004  002  天河区

（所影响的行数为 2 行）
*/

--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

（所影响的行数为 7 行）
*/

drop table tb
drop function f_cid



SQL code
@@ROWCOUNT：返回受上一语句影响的行数。
返回类型：integer。
注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。

UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
   print 'Warning: No rows were updated'

结果：

(所影响的行数为 0 行)
Warning: No rows were updated


 
 
 
/*
标题：SQL SERVER 2005中查询指定节点及其所有子节点的方法(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2010-02-02
地点：新疆乌鲁木齐
*/

create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null  , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
go

DECLARE @ID VARCHAR(3)

--查询ID = '001'的所有子节点
SET @ID = '001'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

(10 行受影响)
*/

--查询ID = '002'的所有子节点
SET @ID = '002'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
002  001  广州市
004  002  天河区

(2 行受影响)
*/

--查询ID = '003'的所有子节点
SET @ID = '003'
;WITH T AS
(
  SELECT ID , PID , NAME 
  FROM TB
  WHERE ID = @ID
  UNION ALL
  SELECT A.ID , A.PID , A.NAME 
  FROM TB AS A JOIN T AS B ON A.PID = B.ID
)
SELECT * FROM T ORDER BY ID
/*
ID   PID  NAME
---- ---- ----------
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇

(7 行受影响)
*/

drop table tb

--注：除ID值不一样外，三个SQL语句是一样的。

 
 

[gw6328]

cte

[randee_luo]

CREATE TABLE TB
(
 id int,
 pid int,
 Name varchar(10)
)
Insert into 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'
select * from TB 

select * from TB where pid in (1,2)
union all
select a.* from TB a,(select * from TB where pid in (1,2)) b where a.pid=b.id 

drop table tb

[Dominic]

谢谢大家。。

jxlhc09，可能是我没说清楚，你的那方法是不行的，因为ID，PID是可能无限次循环的；

另外，我想问问，如何把这个结果和其他的选择结果Union起来？

如：
select * from TB where id=3 --假设这是结果集1

--结果集2
;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

结果集1和结果集2怎么Union？

[王向飞]

;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
union all
select * from TB where ID=3

选中，一起查。

[--小F--]

select * from TB where id=3 
union all
SELECT * FROM aa

--这样就可以了

[Rockets_NBA]

;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 ID,PID,Name  from TB where id=3 
union all 
SELECT * FROM aa

[Rockets_NBA]

[Quote=引用 14 楼 fredrickhu 的回复:]
SQL code
select * from TB where id=3
union all
SELECT * FROM aa

--这样就可以了
[/Quote]小F mm 好啊

[Dominic]

搞定，谢谢大家

[王向飞]

--> 数据库版本:
--> 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

[飘零一叶]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
 id int,
 pid int,
 Name varchar(10)
)
Insert into 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'


;with cte as
(
   select * from TB where id=1 or id=2
   union all
   select t.* from TB t join cte c on t.pid=c.id 
)
select * from cte where pid is not null order by pid 
-----------
id	pid	Name
4	1	name4
6	1	name6
5	2	name5
7	2	name7
9	4	name9

[billpu]

看起来都cte了 2000没市场了

[Dominic]

有没有CTE比较简单的介绍？我看了帮助，没怎么看懂

[Dominic]

如果是2000的库，怎么办？

[billpu]

2000需要用while递归调用
参考
http://blog.csdn.net/zjcxc/archive/2003/12/29/20073.aspx

[飘零一叶]

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，如果超过默认或指定的，则会报错．通常我们用一个层数列来过滤指定的层．