SQL Server 数据库中的递归CTE查询

表名“TH_PRIVILEGE”

查询语句：with tt(l,ID,NAME,PID) as(
select l=case when PID IS null then 1 when PID IS not null then 2 end,ID,NAME,PID from TH_PRIVILEGE
union all
select l=case when t1.PID IS null then 1 when t1.PID IS not null then 2 end,t1.ID,t1.NAME,t1.PID from TH_PRIVILEGE t1
inner join
    tt b         
    on t1.ID=b.PID
)
select distinct(ID),NAME,PID,l from tt;

查询结果：