sql递归查询

--创建表EnterPrise
create table EnterPrise
(
  DeptID [int] IDENTITY(1,1) NOT NULL,--部门编号
  Department nvarchar(50),--部门名称
  ParentDept nvarchar(50),--上级部门
  DepartManage nvarchar(30)--部门经理
)

--给表EnterPrise插入数据
insert into EnterPrise select '技术部','总经办','Tom'
insert into EnterPrise select '商务部','总经办','Jeffry'
insert into EnterPrise select '商务一部','商务部','ViVi'
insert into EnterPrise select '商务二部','商务部','Peter'
insert into EnterPrise select '程序组','技术部','GiGi'
insert into EnterPrise select '设计组','技术部','yoyo'
insert into EnterPrise select '专项组','程序组','Yue'
insert into EnterPrise select '总经办','','Boss'

--递归查询全部信息
with t1 as
( select a.deptid,a.Department,a.ParentDept from EnterPrise a
where a.deptid=8
union all
select  d.deptid,d.Department,d.ParentDept from EnterPrise d
inner join EnterPrise c on d.ParentDept=c.Department )
select * from t1

--查询部门经理是Tom的下面的部门信息

with t2 as
(
   select *,0 as rank from EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from EnterPrise h join t2 h1 on h.ParentDept=h1.Department
)
select * from t2

--查询部门经理是GiGi的上级部门名称
with t3 as
(
   select *,0 as rank from EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from EnterPrise h join t3 h1 on h.Department=h1.ParentDept
)
select * from t3
/*