sql server 使用递归 有些不明白的地方，请教

[weimei521]

员工表BL_TBL_Staff，其中DeptID表示部门ID

部门信息表BL_TBL_Dept 是通过FatherDeptID 来表示上一级部门ID,有可能多层级关系，现在要查询员工的一级部门名字，
不知道在递归里面要union all几次.请教各位大神

[Dear SQL(燊)]

要可以加一列Lvl,就可以看出来了

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([id] int,[pid] int,[num] int)
Insert T
select 1,0,1 union all
select 2,1,1 union all
select 3,2,1 union all
select 4,2,1 union all
select 5,2,1 union all
select 6,3,1 union all
select 7,3,1
Go
--测试数据结束 
;
WITH cte AS (
SELECT *,id AS sumid,Lvl=0
FROM dbo.T
UNION ALL
SELECT T.*,cte.sumid,Lvl=cte.Lvl+1
FROM T JOIN cte ON T.pid=cte.ID
)
SELECT * 
FROM cte 

/*
id          pid         num         sumid       Lvl
----------- ----------- ----------- ----------- -----------
1           0           1           1           0
2           1           1           2           0
3           2           1           3           0
4           2           1           4           0
5           2           1           5           0
6           3           1           6           0
7           3           1           7           0
6           3           1           3           1
7           3           1           3           1
3           2           1           2           1
4           2           1           2           1
5           2           1           2           1
6           3           1           2           2
7           3           1           2           2
2           1           1           1           1
3           2           1           1           2
4           2           1           1           2
5           2           1           1           2
6           3           1           1           3
7           3           1           1           3

(20 行受影响)


*/

[weimei521]

[quote=引用 1 楼 Dear SQL 的回复:]
那现在有一个b表，字段id,name,Tid。其中一条数据是1，测试，7，那怎么查询结果是1，测试，2，因为Tid为7 对面的最上级节点的id就是2，难道是一层一层关联cte 查询吗

[Dear SQL(燊)]

引用 2 楼 weimei521 的回复:

[quote=引用 1 楼 Dear SQL 的回复:] 那现在有一个b表，字段id,name,Tid。其中一条数据是1，测试，7，那怎么查询结果是1，测试，2，因为Tid为7 对面的最上级节点的id就是2，难道是一层一层关联cte 查询吗

先找出每个部门的顶级再去关联

with BL_TBL_DEPT as(
	select deptid=1,deptname='工程部',fatherdeptid='-1' union all
	select deptid=2,deptname='财务部',fatherdeptid='-1' union all
	select deptid=3,deptname='工程部A组',fatherdeptid='1' union all
	select deptid=4,deptname='工程部B组',fatherdeptid='1' union all
	select deptid=4,deptname='工程部A1组',fatherdeptid='3' 
),list as(
	select dept=deptid,deptid,deptname,fatherdeptid,lvl=0,paths=cast(deptid as nvarchar(500))
	from BL_TBL_DEPT
	union all
	select b.dept,a.deptid,a.deptname,a.fatherdeptid,lvl=b.lvl+1
		,paths=cast(paths+'\'+cast(a.deptid as nvarchar(50)) as nvarchar(500))
	from BL_TBL_DEPT a
	inner join list b on a.deptid=b.fatherdeptid

),listdata as(
	select *,rid=ROW_NUMBER()over(partition by dept order by lvl desc)
	from list
)
select *
from listdata
where rid=1
/*

dept        deptid      deptname   fatherdeptid lvl         paths                                                                                                                                                                                                                                                            rid
----------- ----------- ---------- ------------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
1           1           工程部        -1           0           1                                                                                                                                                                                                                                                                1
2           2           财务部        -1           0           2                                                                                                                                                                                                                                                                1
3           1           工程部        -1           1           3\1                                                                                                                                                                                                                                                              1
4           1           工程部        -1           2           4\3\1        
*/

[weimei521]

错了，7对应的最上级节点的id是1

[weimei521]

好了，谢谢，解决了