日期:2014-05-17 浏览次数:20438 次
with hgo as ( select *,0 as rank from #EnterPrise where DepartManage='Tom' union all select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department )
create table #EnterPrise ( Department nvarchar(50),--部门名称 ParentDept nvarchar(50),--上级部门 DepartManage nvarchar(30)--部门经理 ) 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' --查询部门经理是Tom的下面的部门名称 ;with hgo as ( select *,0 as rank from #EnterPrise where DepartManage='Tom' union all select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department ) select * from hgo