MS SQL 遞回查詢

在許多Table中會有資料自我參照的情況，
例如部門代號表，會有組織階層的關係，
若想要列出部門的階層關係，從MS SQL Server 2005開始，
便支援遞回查詢功能，可以很簡單的表示出我們要的結果！

/*------------------------------------------------------------------*/
--建立測試資料
if (select OBJECT_ID('tempdb..#Dep')) IS NOT NULL DROP TABLE #Dep
CREATE TABLE #Dep (DEPNo varchar(5),
                    DEPNa nvarchar(20),
                    FDEPNo varchar(5))

INSERT INTO #Dep Values ('0100','管理部',NULL)
INSERT INTO #Dep Values ('0200','財務部',NULL)
INSERT INTO #Dep Values ('0210','會計課','0200')
INSERT INTO #Dep Values ('0211','應收組','0210')
INSERT INTO #Dep Values ('0212','應付組','0210')
INSERT INTO #Dep Values ('0213','稅務組','0210')
INSERT INTO #Dep Values ('0214','國際會計組','0210')
INSERT INTO #Dep Values ('0220','財務課','0200')
INSERT INTO #Dep Values ('0221','出納組','0220')
INSERT INTO #Dep Values ('0222','銀行相關組','0220')

/*------------------------------------------------------------------*/
WITH Dep_CTE (FDEPNo,DEPNo,DEPNa,DEP_PATH,DEP_LEVEL) AS
(    
    --頂層
    select FDEPNo,DEPNo,DEPNa,CAST(DEPNa as varchar(MAX)),0 AS DT_LEVEL
    from #Dep
    where FDEPNo is NULL
    
    union all    
    
    --成員
    select D.FDEPNo,D.DEPNo,D.DEPNa,CAST(DC.DEP_PATH+' > '+D.DEPNa as varchar(MAX)),DC.DEP_LEVEL+1
    from #Dep D INNER JOIN Dep_CTE DC on D.FDEPNo=DC.DEPNo
)

select * from Dep_CTE
/*------------------------------------------------------------------*/

參考自
MSDN-使用一般資料表運算式的遞迴查詢
Dot Robin Notes-CTE遞回查詢-範例資料庫