100分求解。。有关树的递归查询

Question

--创建测试数据环境
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

Answer 1

--测试环境
declare @t table( id int identity(1,1),name varchar(10),ParentID int)
insert into @t select 'a',''
union all select 'b',''
union all select 'a1',1
union all select 'a2',1
union all select 'b1',2
union all select 'a11',3
union all select 'a12',3
union all select 'b11',5
union all select 'b12',5
union all select 'b13',5
union all select 'a111',6
union all select 'a112',6
union all select 'a113',6

Answer 2

--1d=5 的情况
DECLARE @id AS int
SET @id=5;
WITH TCTE(ID,name,ParentID,LVL)
AS
( 
 SELECT ID,name,ParentID,0
 FROM @t WHERE id=@id
 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 id=@id
 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 * FROM T_CTE
UNION
SELECT * FROM TCTE--结果:
/*
ID          name       ParentID    LVL
----------- ---------- ----------- -----------
2           b          0           1
5           b1         2           0
8           b11        5           1
9           b12        5           1
10          b13        5           1(5 行受影响)
*/

Answer 3

--1d=6 的情况
DECLARE @id AS int
SET @id=6;
WITH TCTE(ID,name,ParentID,LVL)
AS
( 
 SELECT ID,name,ParentID,0
 FROM @t WHERE id=@id
 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 id=@id
 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 * FROM T_CTE
UNION
SELECT * FROM TCTE/*
ID          name       ParentID    LVL
----------- ---------- ----------- -----------
1           a          0           2
3           a1         1           1
6           a11        3           0
11          a111       6           1
12          a112       6           1
13          a113       6           1(6 行受影响)
*/

Answer 4

declare @t table(id varchar(10),Name varchar(10), parentId varchar(10))
insert into @t  values('001',   'A' ,         null)
insert into @t  values('002',   'B' ,         '001')
insert into @t  values('003',   'C' ,         '001')
insert into @t  values('004',   'D' ,         null)
insert into @t  values('005',   'E' ,         '002')
insert into @t  values('006',   'F' ,         '004')
insert into @t  values('007',   'G' ,         '003')
declare @restlt table(id varchar(10),Name varchar(10), parentId varchar(10),path varchar(1000))insert into  @restlt
select id,Name,parentId,id from @t where  parentId is null or parentId not in (select id from  @t )while exists(select * from @t as a,@restlt  as b where a.parentId=b.id and a.id not in (select id from @restlt)
)
insert into @restlt
select a.*, b.path+'/'+a.id from @t as a,@restlt  as b where a.parentId=b.id and a.id not in (select id from @restlt)select * from  @restlt order by path,id
id         Name       parentId   path
001        A          NULL       001
002        B          001        001/002
005        E          002        001/002/005
003        C          001        001/003
007        G          003        001/003/007
004        D          NULL       004
006        F          004        004/006
sql 2005 用 with

Answer 5

--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 行受影响)*/