sql数据tree

;with temp(Id,ParentId,DepartmentName,curLevel,pLevel,haveChild)
as
(
--初始查询
select Id,ParentId,DepartmentName,1 as level,0 as pLevel,1 as haveChild from dbo.Sys_Department
where Enabled = 1 and ParentId = 0   
union all
--递归条件
select a.Id,a.ParentId,a.DepartmentName, b.curLevel+1,b.curLevel,haveChild = (case when exists(select 1 from Sys_Department where Sys_Department.ParentId=a.Id) then 1 else 0 end)
from Sys_Department a
inner join
temp b
on ( a.ParentId=b.Id)
)
select * from temp order by pLevel