介绍:
Department 部门表
DepartmentId 每个部门的部门编号
ParentId 部门的上级部门编号
//查询指定部门的所有下级部门(包括指定部门)
with _a as (
select * from [HRMDB].[dbo].[Department] where DepartmentId = '9C33F2A9-C5CD-44C2-9827-20798A188261'
union all
select a.* from [HRMDB].[dbo].[Department] a
join _a b on a.ParentId = b.DepartmentId
)
select * from _a
//查询指定部门的所有上级部门(包含指定部门)
with _a as (
select * from [HRMDB].[dbo].[Department] where DepartmentId = '9C33F2A9-C5CD-44C2-9827-20798A188261'
union all
select a.* from [HRMDB].[dbo].[Department] a
join _a b on a.DepartmentId= b.ParentId
)
select * from _a
两者的区别就在于最后的join
join _a b on a.ParentId = b.DepartmentId 指定的部门为父节点,查询所有下级部门的
join _a b on a.DepartmentId= b.ParentId 指定的部门单位子节点,查询所有上级部门的
参考于:参考链接