mssql sqlserver with cte表达式(递归)找出最顶值的方法分享


摘要:
下文通过递归的方式找出最顶级部门的方法分享,如下所示:
实验环境:sql server 2008 R2


下文通过cte-with表达式实现递归,获取一个公司的顶级部门,如下所示
例:部门表

create table [maomao365.com]
(keyId int ,parentId int,
deptName nvarchar(30))

insert into [maomao365.com]
(keyId,parentId,deptName)
values
(1,0,'总经办'),
(2,0,'IT中心'),
(10,1,'销售部'),
(11,1,'售后部'),
(111,11,'售后1'),
(1111,111,'售后1_1'),
(12,1,'市场部'),
(21,2,'运维部'),
(22,2,'开发部')

---例1:获取 售后部keyId=1111所在的顶级部门
;
with testA( [keyId], [parentid],deptName)
as
(
select keyId, parentid,deptName
from [maomao365.com]
where keyId = 1111
union all
select a.keyId, a.parentid,a.deptName
from [maomao365.com] a
inner join testA on a.[keyId] = testA.[parentId]  --递归
)

select * from testA where parentId=0;


go

truncate table [maomao365.com]
drop  table [maomao365.com]