sql迭代（with）查询上下级等，以及sqlserver的行列转换

1 .从下往上迭代找上级

emp表

1 为了便于分析，先拿一个员工

select * from emp a

where a.empno in ('7369')

2 用with迭代出7369的所有上级（迭代里面不能用外部关联）

with  tt as
(
select a.empno ,a.ENAME,a.mgr,b.ENAME  as mgr_ENAME,count(b.EMPNO)over(partition by a.empno) as rn
from emp a
left join emp b on a.MGR=b.EMPNO
where a.empno in ('7369')--,'7566','7839','7902')

union all

select a.empno,a.ENAME,b.MGR,c.ename as mgr_ENAME,rn+1 as rn
from  tt  a
inner join emp b on a.MGR=b.EMPNO
inner join emp c on b.MGR=c.EMPNO
 
) 
select * from tt 

3. 给该员工评定等级（最上层领导为1）

with  tt as
(
select a.empno ,a.ENAME,a.mgr,b.ENAME  as mgr_ENAME,count(b.EMPNO)over(partition by a.empno) as rn
from emp a
left join emp