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 方案 1：遞歸 CTE (Common Table Expression) - 推薦 (2005+)

WITH OrgTree AS (
    -- 錨點成員
    SELECT org_id, supervision_org_id, 1 AS lvl, 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.lvl + 1, ot.path + '->' + CAST(o.org_id AS VARCHAR(MAX))
    FROM organizations o
    INNER JOIN OrgTree ot ON o.supervision_org_id = ot.org_id
    WHERE ot.lvl < 100  -- 防無限遞歸
)
SELECT org_id, supervision_org_id, lvl, path
FROM OrgTree
ORDER BY path;

**優點：** 標準 SQL、可讀性強、優化器支持、可輸出層級/路徑/深度。

方案 2：WHILE 循環 + 臨時表 (2000/舊版兼容)

DECLARE @tmp TABLE (org_id INT, supervision_org_id INT, lvl INT, path VARCHAR(MAX));
DECLARE @lvl INT = 1;

-- 初始化根節點
INSERT INTO @tmp (org_id, supervision_org_id, lvl, path)
SELECT org_id, supervision_org_id, 1, CAST(org_id AS VARCHAR(MAX))
FROM organizations WHERE org_id = @org_id;

WHILE @@ROWCOUNT > 0
BEGIN
    SET @lvl = @lvl + 1;
    INSERT INTO @tmp (org_id, supervision_org_id, lvl, path)
    SELECT o.org_id, o.supervision_org_id, @lvl, t.path + '->' + CAST(o.org_id AS VARCHAR(MAX))
    FROM organizations o
    INNER JOIN @tmp t ON o.supervision_org_id = t.org_id
    WHERE t.lvl = @lvl - 1
      AND NOT EXISTS (SELECT 1 FROM @tmp WHERE org_id = o.org_id); -- 防重複/循環
    IF @lvl > 100 BREAK; -- 安全閾值
END

SELECT * FROM @tmp ORDER BY path;

方案 3：函數封裝 (複用性)

CREATE FUNCTION dbo.GetOrgHierarchy (@RootOrgId INT)
RETURNS @Result TABLE (org_id INT, supervision_org_id INT, lvl INT, path VARCHAR(MAX))
AS
BEGIN
    WITH OrgTree AS (
        SELECT org_id, supervision_org_id, 1 AS lvl, CAST(org_id AS VARCHAR(MAX)) AS path
        FROM organizations WHERE org_id = @RootOrgId
        UNION ALL
        SELECT o.org_id, o.supervision_org_id, ot.lvl + 1, ot.path + '->' + CAST(o.org_id AS VARCHAR(MAX))
        FROM organizations o INNER JOIN OrgTree ot ON o.supervision_org_id = ot.org_id
        WHERE ot.lvl < 100
    )
    INSERT INTO @Result SELECT * FROM OrgTree;
    RETURN;
END;
-- 調用
SELECT * FROM dbo.GetOrgHierarchy(1) ORDER BY path;

功能型層級結構 (菜單/權限樹) 實現

-- 表結構
CREATE TABLE FuncTree (
    FuncNo VARCHAR(10),       -- 01, 01.01
    SubFuncNo VARCHAR(10),    -- 子功能編碼
    SubFuncType BIT           -- 0=葉子, 1=分支
);

-- CTE 查詢完整樹
WITH FuncHierarchy AS (
    SELECT FuncNo, SubFuncNo, SubFuncType, 1 AS lvl, CAST(FuncNo AS VARCHAR(MAX)) AS path
    FROM FuncTree WHERE FuncNo = '01'  -- 根
    UNION ALL
    SELECT f.FuncNo, f.SubFuncNo, f.SubFuncType, fh.lvl + 1, fh.path + '.' + f.SubFuncNo
    FROM FuncTree f INNER JOIN FuncHierarchy fh ON f.FuncNo = fh.SubFuncNo
    WHERE fh.lvl < 20
)
SELECT * FROM FuncHierarchy ORDER BY path;

性能優化建議

• **索引：** `CREATE INDEX IX_Org_Supervision ON organizations(supervision_org_id) INCLUDE (org_id);`
• **最大遞歸層級：** `OPTION (MAXRECURSION 1000)` (默認 100，0=無限制)。
• **大數據量分頁：** CTE 物化後再分頁，或使用 Keyset 分頁 (`WHERE path > @last_path`)。
• **循環檢測：** 數據質量保證無循環，或 CTE 中加 `path NOT LIKE '%' + CAST(org_id AS VARCHAR) + '%'` (性能差)。

MySQL 8.0+ / PostgreSQL 同樣支持遞歸 CTE

語法與 SQL Server 基本一致，`WITH RECURSIVE` 關鍵字 (MySQL 8.0.1+ / PG 全版本)。