WITH (遞迴)
遞迴 CTE 結構至少必須包含一個錨點成員與一個遞迴成員。下列虛擬程式碼顯示含有單一錨點成員與單一遞迴成員的簡單遞迴 CTE 所具備的元件。
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
SELECT * FROM cte_name
CTE 中不可以使用以下的子句：
1.COMPUTE 或 COMPUTE BY
2.ORDER BY (除非指定了 TOP 子句)
3.INTO
4.含有查詢提示的 OPTION 子句
5.FOR XML
6.FOR BROWSE
範例1：
資料清單
--如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
--Recursive CTE分為兩個部分, 第一部分為Anchor Member，指不會被遞迴呼叫到的部分
SELECT
部門名稱,
直屬部門,
0,
CONVERT(nvarchar(128), 部門ID)
FROM dbo.公司組織
WHERE 直屬部門=N'ROOT'
UNION ALL
--UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,直到無任何查詢結果為止
SELECT
P.部門名稱,
P.直屬部門,
B.層級+1,
CONVERT(nvarchar(128), B.排序欄位 + '-' + CONVERT(nvarchar(128), P.部門ID))
FROM dbo.公司組織 P, 公司組織結果 B
WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE(' ', 層級) + 部門名稱) as '部門名稱', 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位
範例2：
with myview (sys_dep_up,sys_dep_id )
as
(
select
sys_dep_up,
sys_dep_id
from dbo.sys_dep
where sys_dep_up = 301
union all
select
a.sys_dep_up,
a.sys_dep_id
from dbo.sys_dep a,myview b
where a.sys_dep_up=b.sys_dep_id
)
select sys_dep_id as alldepid
from myview