SQL Server递归查询

原料

--创建组织架构表
create table Org(
    OrgId nvarchar(50) primary key,
    ParentId nvarchar(50),
    OrgName nvarchar(50)
)
--添加数据
insert into Org (OrgId,ParentId,OrgName) values('00',null,'***集团')
insert into Org (OrgId,ParentId,OrgName) values('01','00','子公司01')
insert into Org (OrgId,ParentId,OrgName) values('02','00','子公司02')
insert into Org (OrgId,ParentId,OrgName) values('03','00','子公司03')
insert into Org (OrgId,ParentId,OrgName) values('04','00','子公司04')

insert into Org (OrgId,ParentId,OrgName) values('0101','01','电子商务部')
insert into Org (OrgId,ParentId,OrgName) values('0102','01','渠道合作部')
insert into Org (OrgId,ParentId,OrgName) values('0103','01','个人业务部')
insert into Org (OrgId,ParentId,OrgName) values('0104','01','人力资源部')
insert into Org (OrgId,ParentId,OrgName) values('0105','01','运营管理部')

insert into Org (OrgId,ParentId,OrgName) values('0201','02','电子商务部')
insert into Org (OrgId,ParentId,OrgName) values('0202','02','渠道合作部')
insert into Org (OrgId,ParentId,OrgName) values('0203','02','个人业务部')
insert into Org (OrgId,ParentId,OrgName) values('0204','02','人力资源部')
insert into Org (OrgId,ParentId,OrgName) values('0205','02','运营管理部')

insert into Org (OrgId,ParentId,OrgName) values('0301','03','电子商务部')
insert into Org (OrgId,ParentId,OrgName) values('0302','03','渠道合作部')
insert into Org (OrgId,ParentId,OrgName) values('0303','03','个人业务部')
insert into Org (OrgId,ParentId,OrgName) values('0304','03','人力资源部')
insert into Org (OrgId,ParentId,OrgName) values('0305','03','运营管理部')

insert into Org (OrgId,ParentId,OrgName) values('0401','04','电子商务部')
insert into Org (OrgId,ParentId,OrgName) values('0402','04','渠道合作部')
insert into Org (OrgId,ParentId,OrgName) values('0403','04','个人业务部')
insert into Org (OrgId,ParentId,OrgName) values('0404','04','人力资源部')
insert into Org (OrgId,ParentId,OrgName) values('0405','04','运营管理部')

1.向下递归

--向下递归
with temp (OrgId,ParentId,OrgName)
as
(
select OrgId,ParentId,OrgName from Org
where OrgId='01'
union all
select a.OrgId, a.ParentId,a.OrgName from Org a
inner join temp on a.[ParentId] = temp.[OrgId]
)
select * from temp

输出结果：

 

2.向上递归

--向上递归
with temp (OrgId,ParentId,OrgName)
as
(
select OrgId,ParentId,OrgName from Org
where OrgId='0405'
union all
select a.OrgId, a.ParentId,a.OrgName from Org a
inner join temp on a.[OrgId] = temp.[ParentId]
)
select * from temp

输出结果：