100分求解。。有关树的递归查询
现有一个表，结构如下
ID Name ParentID
1 a
2 b
3 a1 1
4 a2 1
5 b1 2
6 a11 3
7 a12 3
8 b11 5
9 b12 5
10 b13 5
11 a111 6
12 a112 6
13 a113 6
.....
要求随意取出表中一条或几条记录，得出完整的相关联的树结构
即，若取出的是ID为5，6的2条记录要求的到
ID Name ParentID
2 b
5 b1 2
8 b11 5
9 b12 5
10 b13 5
1 a
3 a1 1
6 a11 3
11 a111 6
12 a112 6
13 a113 6
也就是要的到与取出记录相关的所有的父与子的记录
麻烦各位高手帮忙啦
[解决办法]
--创建测试数据环境
create table test(ID int,Name varchar(10),ParentID int)
insert into test select 1 , 'a ',null
insert into test select 2 , 'b ',null
insert into test select 3 , 'a1 ',1
insert into test select 4 , 'a2 ',1
insert into test select 5 , 'b1 ',2
insert into test select 6 , 'a11 ',3
insert into test select 7 , 'a12 ',3
insert into test select 8 , 'b11 ',5
insert into test select 9 , 'b12 ',5
insert into test select 10, 'b13 ',5
insert into test select 11, 'a111 ',6
insert into test select 12, 'a112 ',6
insert into test select 13, 'a113 ',6
go
--创建用户定义函数
create function f_test(@ID int)
returns varchar(4000)
as
begin
declare @RET varchar(4000),@ParentID int
set @RET = right( '0000 '+rtrim(@ID),4)
while exists(select 1 from test where ID=@ID and ParentID is not null)
begin
select @ParentID=ParentID from test where ID=@ID and ParentID is not null
set @ID = @ParentID
set @RET = right( '0000 '+rtrim(@ID),4)+@RET
end
return @RET
end
go
--创建存储过程
create procedure sp_test(@ID varchar(100))
as
begin
declare @NID varchar(400)
select dbo.f_test(ID) as NID,* into # from test
declare @t table(ID int,Name varchar(10),ParentID int)
set @ID=@ID+ ', '
while charindex( ', ',@ID)> 0
begin
print left(@ID,charindex( ', ',@ID)-1)
select @NID=NID from # where ID=left(@ID,charindex( ', ',@ID)-1)
insert into @t(ID,Name,ParentID)
select
ID,Name,ParentID
from
# t
where
NID like @NID+ '% ' or @NID like NID+ '% '
and
not exists(select 1 from @t where ID=t.ID)
order by NID
set @ID =stuff(@ID,1,charindex( ', ',@ID), ' ')
end
select * from @t
end
go
--执行存储过程，查看结果
exec sp_test '5,6 '
go
/*
ID Name ParentID
----------- ---------- -----------
2 b NULL
5 b1 2
8 b11 5
9 b12 5
10 b13 5
1 a NULL
3 a1 1
6 a11 3
11 a111 6
12 a112 6
13 a113 6
*/
--清除测试环境
drop procedure sp_test
drop function f_test
drop table test
go
[解决办法]
--1d=5,6 的情况
DECLARE @id AS varchar(200)
SET @id= '5,6 ';
WITH TCTE(ID,name,ParentID,LVL)
AS
(
SELECT ID,name,ParentID,0
FROM @t WHERE charindex( ', '+ltrim(id)+ ', ', ', '+@id+ ', ')> 0
UNION ALL
SELECT ta.ID,ta.name,ta.ParentID,TB.LVL+1
FROM @t TA INNER JOIN TCTE TB
ON TA.ParentID=TB.id
),
T_CTE(ID,name,ParentID,LVL)
AS
(
SELECT ID,name,ParentID,0
FROM @t WHERE charindex( ', '+ltrim(id)+ ', ', ', '+@id+ ', ')> 0
UNION ALL
SELECT ta.ID,ta.name,ta.ParentID,TB.LVL+1
FROM @t TA INNER JOIN T_CTE TB
ON TA.id=TB.ParentID
)
SELECT ID,name,ParentID=case when ParentID=0 then null else ParentID end
FROM TCTE
UNION
SELECT ID,name,ParentID=case when ParentID=0 then null else ParentID end
FROM T_CTE
/*
ID name ParentID
----------- ---------- ----------
1 a NULL
2 b NULL
3 a1 1
5 b1 2
6 a11 3
8 b11 5
9 b12 5
10 b13 5
11 a111 6
12 a112 6
13 a113 6
(11 行受影响)
*/
