sql 如何使用递归进行查询

[阿狸Ahri]

有表结构是DeptID,DeptName,ParentID的表，部门表，ParentID是指上级部门，如果ParentID=0，则表示已经到根目录

如 DeptID,DeptName,ParentID
1 经理室 0
2 课室 1
3 宣传室 2

需要找到每一个课室的最顶级ID

查询之后是

DeptID,DeptName,ParentID
1 经理室 1
2 课室 1
3 宣传室 1

[二月十六]

为什么不显示上一级呢？感觉有点怪。。

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([DeptID] int,[DeptName] nvarchar(23),[ParentID] int)
Insert #T
select 1,N'经理室',0 union all
select 2,N'课室',1 union all
select 3,N'宣传室',2
Go
--测试数据结束
;WITH cte AS (
SELECT  DeptID ,
        DeptName ,
        CASE WHEN ParentID = 0 THEN DeptID
             ELSE ParentID
        END AS ParentID
FROM    #T
WHERE   ParentID = 0
UNION ALL
SELECT a.DeptID,a.DeptName,b.DeptID FROM #T a JOIN cte b ON a.ParentID = b.DeptID
)
SELECT DeptID,DeptName,(SELECT MIN(ParentID) FROM cte) AS ParentID FROM cte 

[0与1之间]

With T1(DeptID,DeptName,ParentID) as 
(
select 1,N'经理室',0 union all
select 2,N'课室',1 union all
select 3,N'宣传室',2
),T2 as 
(
select *,ID=deptid from T1 where ParentID=0 Union All
Select b.*,ID from T2 a Cross Apply T1 b where a.DeptID=b.ParentID
)
Select DeptID,DeptName,ID as ParentID from T2