下边是个递归算法，我也执行过。with as知道是公用表表达式（CTE）
但是就是看不懂下边语句，不知道该怎么读
- SQL code
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)
完整：
- SQL code
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
------解决方案--------------------
那你只留2条数据,看看结果,这个东西要意会,比较难言传
------解决方案--------------------
这个能看懂吧
这个是第一次递归的结果
然后
select h.*,h1.rank+1 from #EnterPrise h join
(select *,0 as rank from #EnterPrise where DepartManage='Tom')
h1 on h.ParentDept=h1.Department
第二次
select h.*,h1.rank+1 from #EnterPrise h join
(
select h.*,h1.rank+1 from #EnterPrise h join
(select *,0 as rank from #EnterPrise where DepartManage='Tom')
h1 on h.ParentDept=h1.Department
)
h1 on h.ParentDept=h1.Department
第三次
然后 知道玩完