SQL server資料庫with as子句與遞迴查詢的實現

sql server中遞迴併不與Oracle等資料庫中的遞迴一樣。在sql server中遞迴使用join自連線完成的，當然這些都要在with as短句中完成的，下面我們先來說一下with as 短句。

1.關於with as短句

WITH AS短語，也叫子查詢部分（subquery factoring），可以定義一個SQL片斷，該SQL片斷會被整個SQL語句用到。可以使SQL語句的可讀性更高，也可以在UNION ALL的不同部分，作為提供資料的部分。

對於UNION ALL，使用WITH AS定義了一個UNION ALL語句，當該片斷被呼叫2次以上，優化器會自動將該WITH AS短語所獲取的資料放入一個Temp表中。而提示meterialize則是強制將WITH AS短語的資料放入一個全域性臨時表中。很多查詢通過該方式都可以提高速度。

with cet as(
    select id name,age from stuents where Name like '劉%' 
)
--因為with as是一種子句形式，所以它的使用必須依賴於主查詢語句，否則會報錯
select * from cet

/*
**當然了，我是更喜歡這種形式去寫with as子句，
**這樣看起來更加清晰明瞭
**
with cet(id,name,age)as(
    select id name,age from stuents where Name like '劉%' 
)
*/

2.遞迴查詢使用

例如，這張表應該是一個樹的表格，我們現在運用遞迴查詢，要將樹的每一個節點標註上節點層級序號，查出這張表的資料以及每條資料對應樹節點的層級(例如：世界層級是1，中國、日本的樹層級是2)。

WITH cte(id,name,pid,sorder,slevel)AS(
--先找到遞迴的起點
SELECT id,name,pid,shoe_order as sorder,1 as slevel  FROM tree_demo WHERE pid=0
--用join從起點開始連線形成遞迴查詢，然後用union all拼接上起點
UNION ALL
SELECT b.id,b.name,b.pid,b.shoe_order as sorder,c.slevel+1 as slevel  FROM tree_demo b
JOIN cte c on b.pid=c.id
)
SELECT * FROM cte

首先我先找到樹的根節點(節點名稱是'世界'的節點，也就是pid=0)，作為本次遞迴查詢的起點，然後查詢表格並將表格資料與子句查詢結果做關聯查詢從而形成遞迴，在最後將根節點與遞迴節點用union all拼接到一起，這便是遞迴查詢的最後結果了。

感謝大家的閱讀，也真心希望大家能有所收穫，同樣也感謝大家能提出寶貴的意見或者是建議，讓更多的人在技術這條路上走得更加坦蕩。