sql 递归

[小小骨头]

原表：
ID 姓名 角色 Num 父Num
1. 001 小明 学生 A001 A002
2. 002 小王 学生 A002 A003
3. 003 小张 老师 A003 A000

条件：ID=001
结果：
3. 小张

我的业务是查找改人员父Num，角色为老师的人员姓名，通过递归查找。
我一层的SQL如下：

select 姓名 from 人员 where Num=(select 父Num from 人员 where ID=001)and 角色='老师'

怎么改造成递归？

[jayzhihui]

这个是根据子节点返回最上层父节点。。。。 网上很多递归列子，不写了！

[xdashewan]

with cte as
(
    select ID,       姓名,     角色,    Num,     父Num from 原表
    where Id = '001'
    union all
    select a.ID,       a.姓名,     a.角色,    a.Num,     a.父Num from cte c inner join Department a    
    on c.父Num = a.Num)
select * from cte where 角色 = '老师'

[guguda2008]

如果老师还有老师，下面这个SQL查找最顶级的老师

DECLARE @ID VARCHAR(100)
SELECT @ID=001
;WITH MU AS (
SELECT ID,NAME,NUM,父NUM,角色,1 AS LEVEL
FROM TB
WHERE ID=@ID
UNION ALL
SELECT T1.ID,T1.NAME,T1.NUM,T1.父NUM,T1.角色,MU.LEVEL+1
FROM TB T1
INNER JOIN MU T2 ON T1.NUM=T2.父NUM
)
SELECT 
FROM MU T1
WHERE 角色='老师' AND NOT EXISTS(
SELECT 1 
FROM MU T2
WHERE T2.角色='老师' 
--如果要找最低层的老师，改成AND T2.LEVEL<T1.LEVEL
AND T2.LEVEL>T1.LEVEL
)