递归从上到下的父项及其子项

Question

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

3333

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

谢谢你们的帮助，我自己弄明白了。如果你们中有人能改进我的答案就太好了，这不是最好的解决方案，但目前它是什么