CTE递归

create table test
(
    id int,
    name varchar(10),
    parentid int
)
select * from test

insert into Test values(1,'aa',-1)
insert into Test values(2,'aa',-1)
insert into Test values(3,'aa',1)
insert into Test values(4,'aa',1)
insert into Test values(5,'aa',3)
declare @id int = 1;
with TestCTE as
(
    select * from test where id = @id
     union all
     select test.* from TestCTE join test on test.parentid = TestCTE.id
)