SQL語句中使用 with 遞迴實現表中資料樹狀顯示

標籤：

　　在開發過程中會遇到很多實現樹狀的功能，之前為了實現資料的樹狀顯示一般都是通過程式裡面的遞迴實現，今天試了一下通過sql語句實現具體如下：

　　表名：DeptInfo

　　欄位：DeptId（部門編號），DeptName（部門名稱），DeptUpId（部門上級ID）,DeptPath（部門層級）

　　從DeptUpId和DeptPath中可看出表資料可能很亂：

　　

　　為了實現表中資料樹狀顯示，條例清晰具體代碼實現如下：

with dept as(
select DeptId,DeptUpId from DeptInfo union all select dept.DeptId,DeptInfo.DeptUpId from dept inner join DeptInfoon dept.DeptUpId=DeptInfo.DeptId
) select * from DeptInfo

　　執行後結果如下：

　　

 　　小結一下：使用遞迴時查閱資料後還要注意以下幾點：

　　　　1. 遞迴 CTE 定義至少必須包含兩個 CTE 查詢定義，一個錨點成員和一個遞迴成員。可以定義多個錨點成員和遞迴成員；但必須將所有錨點成員查詢定義置於第一個遞迴成員定義之前。所有 CTE 查詢定義都是錨點成員，但它們引用 CTE 本身時除外。

　　　　2. 錨點成員必須與以下集合運算子之一結合使用：UNION ALL、UNION、INTERSECT 或EXCEPT。在最後一個錨點成員和第一個遞迴成員之間，以及組合多個遞迴成員時，只能使用 UNION ALL 集合運算子。

　　　　3. 錨點成員和遞迴成員中的列數必須一致。

　　　　4. 遞迴成員中列的資料類型必須與錨點成員中相應列的資料類型一致。

　　　　5. 遞迴成員的 FROM 子句只能引用一次 CTEexpression_name。

　　　　6. 在遞迴成員的 CTE_query_definition 中不允許出現下列項：　　　　　

　　　　　　（1）SELECT DISTINCT

　　　　　　（2）GROUP BY

　　　　　　（3）HAVING

　　　　　　（4）標量彙總

　　　　　　（5）TOP

　　　　　　（6）LEFT、RIGHT、OUTERJOIN（允許出現 INNER JOIN）

　　　　　　（7）子查詢

　　　　　　（8）應用於對 CTE_query_definition 中的 CTE 的遞迴引用的提示。

　　　　7. 無論參與的 SELECT 語句返回的列的為空白性如何，遞迴 CTE 返回的全部列都可以為空白。

　　　　8. 如果遞迴 CTE 組合不正確，可能會導致無限迴圈。例如，如果遞迴成員查詢定義對父列和子列返回相同的值，則會造成無限迴圈。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或SELECT 語句的 OPTION 子句中的一個 0 到 32,767 之間的值，來限制特定語句所允許的遞迴級數，以防止出現無限迴圈。這樣就能夠在解決產生迴圈的代碼問題之前控制語句的執行。伺服器範圍內的預設值是 100。如果指定 0，則沒有限制。每一個語句只能指定一個 MAXRECURSION 值。

　　　　9. 不能使用包含遞迴通用資料表運算式的視圖來更新資料。

　　　　10. 可以使用 CTE 在查詢上定義遊標。遞迴CTE 只允許使用快速順向資料指標和靜態（快照）遊標。如果在遞迴 CTE 中指定了其他遊標類型，則該類型將轉換為靜態資料指標類型。

　　　　11. 可以在 CTE 中引用遠程伺服器中的表。如果在 CTE 的遞迴成員中引用了遠程伺服器，那麼將為每個遠端資料表建立一個假離線，這樣就可以在本地反覆訪問這些表。

SQL語句中使用 with 遞迴實現表中資料樹狀顯示