sqlserver 组织结构树查询

-- with一个临时表
with temp(ID,PID,Name,curLevel)
as
(
--1：初始查
select id,pid,Name,1 as level from dbo.org 
where id =1    
union all
--2：递归条件
select a.ID,a.PID,a.Name, b.curLevel+1from org a  
inner join
temp b
on ( a.pId=b.ID) 
)
select * from temp where temp.curLevel=2