SQL Server 递归自顶向下父级及其子级

vm0i2vca 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(133)

declare @table as table
 (
  Id int,
  ParentId int
 )

 insert into @table
 values (1 , null),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3)

  ;with cte 
    as (select Id,ParentId,1 as [level]
        from @table as t 
        where ParentId is null
        UNION ALL
        select t.Id,t.ParentId , cte.level + 1
        from @table as t 
        join cte 
        on t.ParentId = cte.Id)
    select *
    from cte

Id	ParentId	Level
1	NULL	1
2	1	2
3	1	2
6	3	3
7	3	3
4	2	3
5	2	3

I want to get Top to Bottom Parent -> it's all descendants
and again Top to Bottom Parent -> it's descendants
but I am unable to get expected output using above query
expected output vertically in table
Id 1 -> 2 -> 4 -> 5
Id 1 -> 3 -> 6 -> 7

expected output

Id	ParentId	Level
1	NULL	1
2	1	2
4	2	3
5	2	3
1	NULL	1
3	1	2
6	3	3
7	3	3

A tree Starts from 1 and goes to bottom to find it's all child
1 -> 2 -> 4 -> 5
again tree Starts from 1 and goes to bottom to find it's all child
1 -> 3 -> 6 -> 7

sql-server

来源：https://stackoverflow.com/questions/74717538/recursive-top-to-bottom-parent-and-its-child

1条答案

按热度按时间

jdgnovmf1#

declare @table as table
 (
  Id int,
  ParentId int
 )

 declare @cte as table
 (
  Id int,
  ParentId int 
 )

 declare @output as table
 (
  Id int,
  ParentId int
 
 )
  
 insert into @table
 values (1,null),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3)

 declare @id int 
 declare @parentid int
  

  ;with cte 
    as (select Id,ParentId
        from @table as t 
        where ParentId is null
        UNION ALL
        select t.Id,t.ParentId 
        from @table as t 
        join cte 
        on t.ParentId = cte.Id)
     insert into @cte
    select *
    from cte  
     
     
     declare @start int = 1
     declare @end int = (select count(ParentId) from @cte where ParentId= (select Id from @cte where ParentId is null))

     while(@start <= @end)
     begin
     
     select top 1 @id = Id  ,@parentid=ParentId from @cte where ParentId = (select Id from @cte where ParentId is null) 

     if(@id > 0 )
     begin

     insert into @output
     select * from @cte where ParentId is null

     insert into @output values (@id,@parentid)
     delete from @cte where Id = @id and ParentId = @parentid  

     insert into @output
     select * from @cte where ParentId = @id

     delete from @cte where Id  in (select Id from @cte where ParentId = @id)

     end
      
     set @start+=1
     end

     select * from @output

**thank you guys for your help i figured it out by myself.**it would be nice if any of you guys improve my answer, it is not best solution but currently it is what it is

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 递归自顶向下父级及其子级

1条答案

相关问题

热门标签

最新问答