SQL Server 樹狀結構資料儲存與遞歸查詢實作（CTE）

測試資料建立

CREATE TABLE #employees (
    employeecode VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
    reporttocode VARCHAR(20) NULL
);

INSERT INTO #employees VALUES
('a', NULL), ('b','a'), ('c','a'), ('d','a'),
('e','b'), ('f','b'), ('g','c'),
('h','d'), ('i','d'), ('j','d'),
('k','j'), ('l','j'), ('m','j'),
('n','k');

常見查詢需求

• 員工 'd' 的所有直屬下屬
• 員工 'd' 的 2 層以內下屬（含直屬與直屬的下屬）
• 員工 'n' 的所有上級（依回報線順序）
• 指定員工 @employeecode 的 @leveldown 層以內下屬
• 指定員工 @employeecode 的 @levelup 層以內上級

遞歸 CTE 實作完整樹狀關係

WITH cte AS (
    -- 錨點成員：最上層（無上級）
    SELECT
        employeecode,
        reporttocode,
        reporttodepth = 0,
        reporttopath = CAST('/' + employeecode + '/' AS VARCHAR(200))
    FROM #employees
    WHERE reporttocode IS NULL

    UNION ALL

    -- 遞歸成員：往下層展開
    SELECT
        e.employeecode,
        e.reporttocode,
        reporttodepth = mgr.reporttodepth + 1,
        reporttopath = CAST(mgr.reporttopath + e.employeecode + '/' AS VARCHAR(200))
    FROM #employees e
    INNER JOIN cte mgr ON e.reporttocode = mgr.employeecode
)
SELECT * FROM cte ORDER BY reporttopath;

結果欄位說明

進階查詢範例

查詢員工 'd' 的 2 層以內下屬

DECLARE @employeecode VARCHAR(20) = 'd', @leveldown INT = 2;
SELECT * FROM cte
WHERE reporttopath LIKE (SELECT reporttopath FROM cte WHERE employeecode = @employeecode) + '%'
  AND reporttodepth <= (SELECT reporttodepth FROM cte WHERE employeecode = @employeecode) + @leveldown;

查詢員工 'n' 的 2 層以內上級

DECLARE @employeecode VARCHAR(20) = 'n', @levelup INT = 2;
-- 需建立反向 CTE 或利用 path 反向查詢
-- 此處略，概念同理