SqlServer 遞歸查詢樹

遞歸關于進行樹形結構的查詢：

一：簡單的樹形結構代碼。

-- with一個臨時表(括号中是你要查詢的列名)
with temp(ID,PID,Name,curLevel)
as
(
--1：初始查詢（這裡的PID=-1 在我的資料中是最底層的根節點）
select ID,PID,Name,1 as level from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--2：遞歸條件
select a.ID,a.PID,a.Name, b.curLevel+1from T_ACL_OU a   --3：這裡的臨時表和原始資料表都必須使用别名，不然遞歸的時候不知道查詢的是那個表的列
inner join
temp b
on ( a.PID=b.id)  --這個關聯關系很重要，一定要了解一下誰是誰的父節點
)
select * from temp   --4：遞歸完成後 一定不要少了這句查詢語句 否則會報錯
      

 二：帶縮進的樹形機構

with temp(ID,PID,Name,curLevel)
as
(
--初始查詢
select ID,PID,Name,1 as curLevel from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--遞歸條件
select a.ID,a.PID, 
convert(nvarchar(100),CONVERT(nvarchar(100), REPLICATE ('    ', b.curLevel+1)+a.Name)) as Name , b.curLevel+1 
  --這裡的 REPLICATE函數非常重要，用于縮進空格用。不懂得可以在SQLserver中選中後按F1鍵
from T_ACL_OU a 
inner join
temp b
on ( a.PID=b.id)
)
select ID,PID,Name,curLevel from temp
      

with temp(ID,PID,HandNo,Name,curLevel,pLevel,haveChild)
as
(
--初始查詢
select ID,PID,HandNo,Name,1 as level,0 as pLevel,1 as haveChild from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--遞歸條件
select a.ID,a.PID,a.HandNo,a.Name, b.curLevel+1,b.curLevel,haveChild 
= (case when exists(select 1 from T_ACL_OU where T_ACL_OU.PID=a.id) then 1 else 0 end)
--(select 1 from T_ACL_OU where exists(select 1 from T_ACL_OU where a.PID=b.id)) 
from T_ACL_OU a 
inner join
temp b
on ( a.PID=b.id)
)
select * from temp order by pLevel