Oracle CONNECT BY 遞迴查詢在 SQL Server 實現：CTE 遞迴與 WHILE 迴圈對比

Oracle 寫法 (原生支援)

SELECT org_id
FROM organizations
START WITH org_id = :org_id
CONNECT BY PRIOR org_id = supervision_org_id
ORDER BY supervision_org_id;

SQL Server 方案一：遞迴 CTE (Common Table Expression) - 推薦

SQL Server 2005+ 標準語法，集合式、可讀性強。

WITH OrgTree AS (
    -- Anchor member (根節點)
    SELECT org_id, supervision_org_id, 1 AS lvl
    FROM organizations
    WHERE org_id = @org_id

    UNION ALL

    -- Recursive member (遞迴向下)
    SELECT o.org_id, o.supervision_org_id, ot.lvl + 1
    FROM organizations o
    INNER JOIN OrgTree ot ON o.supervision_org_id = ot.org_id
)
SELECT org_id FROM OrgTree
OPTION (MAXRECURSION 100);  -- 防無限遞迴，預設 100，0=無限制

• START WITH → Anchor Member。
• CONNECT BY PRIOR child = parent → Recursive Member JOIN 條件。
• ORDER BY → 外層查詢加 ORDER BY 或 CTE 內加 lvl 排序。

SQL Server 方案二：WHILE 迴圈 + 暫存表 (舊版本相容)

-- 測試資料
DECLARE @a TABLE (TC_Id INT, TC_PID INT, TC_Name VARCHAR(200));
INSERT @a VALUES (1,0,'中國'),(2,0,'美國'),(3,0,'加拿大'),
(4,1,'北京'),(5,1,'上海'),(6,1,'江蘇'),
(7,6,'蘇州'),(8,7,'常熟'),(9,6,'南京'),(10,6,'無錫'),
(11,2,'紐約'),(12,2,'舊金山');

-- 遞迴查詢 TC_Id=1 的所有子孫
DECLARE @tmp TABLE (TC_Id INT, TC_PID INT, TC_Name VARCHAR(200), Lvl INT);
INSERT @tmp SELECT TC_Id, TC_PID, TC_Name, 1 FROM @a WHERE TC_Id = 1;

WHILE EXISTS (
    SELECT 1 FROM @a a
    INNER JOIN @tmp t ON a.TC_PID = t.TC_Id
    WHERE a.TC_Id NOT IN (SELECT TC_Id FROM @tmp)
)
BEGIN
    INSERT @tmp
    SELECT a.TC_Id, a.TC_PID, a.TC_Name, t.Lvl + 1
    FROM @a a
    INNER JOIN @tmp t ON a.TC_PID = t.TC_Id
    WHERE a.TC_Id NOT IN (SELECT TC_Id FROM @tmp);
END

SELECT * FROM @tmp ORDER BY Lvl, TC_Id;

兩種方案對比

進階：模擬 Oracle CONNECT_BY_ROOT / SYS_CONNECT_BY_PATH

-- 取得根節點 ID
SELECT org_id,
       (SELECT TOP 1 org_id FROM OrgTree ot2 WHERE ot2.lvl = 1 AND ot2.org_id = ot.org_id) AS root_id
FROM OrgTree ot;

-- 取得路徑字串 (需 SQL Server 2017+ STRING_AGG 或自行遞迴拼接)
-- 簡易版：在 CTE 中累積路徑
WITH OrgPath AS (
    SELECT org_id, supervision_org_id, CAST(org_id AS VARCHAR(MAX)) AS path
    FROM organizations WHERE org_id = @org_id
    UNION ALL
    SELECT o.org_id, o.supervision_org_id, ot.path + '->' + CAST(o.org_id AS VARCHAR(MAX))
    FROM organizations o
    INNER JOIN OrgPath ot ON o.supervision_org_id = ot.org_id
)
SELECT org_id, path FROM OrgPath;

總結

SQL Server 2005+ **請優先使用遞迴 CTE**，語法對應 Oracle START WITH ... CONNECT BY 直觀、效能佳；舊版本才退而求其次用 WHILE 迴圈。