with递归获取分层数据(备忘)

-- 递归

-- 创建测试表
create table tb_Test(
id int not null
,pId int not null
,content varchar(200)
)

go

-- 测试数据
insert tb_Test (id,pId,content)
values
(1,0,'测试1')
,(2,0,'测试2')
,(3,0,'测试3')
,(4,1,'测试1-1')
,(5,1,'测试1-2')
,(6,1,'测试1-3')
,(7,4,'测试1-1-1')
,(8,7,'测试1-1-1-1')


-- 查询 id = 1(包括)的所有子集
;with test as
(
select id,content,pId from tb_Test where id = 1
union all
select a.id,a.content,a.pId from tb_Test a,test b where a.pId = b.id
)
select * from test


-- 查询 id = 8(包括)的所有父级
;with test as (
select id,content,pId from tb_Test where id = 8 
union all
select a.id,a.content,a.pId from tb_Test a,test b where a.id = b.pId
)
select * from test