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
)
(
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
)