SQL Server 樹狀結構資料排序預存程序：無限級分類完美輸出

應用場景

產品分類、組織架構、選單導覽等「無限級分類」資料，需依層級排序並產生縮排顯示名稱 (如 A、-AA、--AAA)。

環境

Windows 7 + SQL Server 2008 (腳本相容 SQL 2000)

1. 建立資料表與測試資料

CREATE TABLE dbo.Categories (
    category_id INT IDENTITY(1,1) PRIMARY KEY,
    parent_id   INT NULL,
    category_name VARCHAR(500) NULL
)
GO

INSERT INTO Categories (category_id, parent_id, category_name) VALUES
(1, 0, N'A'), (2, 0, N'B'), (3, 1, N'AA'), (4, 3, N'AAA'),
(5, 2, N'BB'), (6, 1, N'AA2'), (7, 1, N'AA3'),
(8, 3, N'AAA2'), (9, 4, N'AAA3'), (10, 2, N'BB2')

2. 預存程序：遞迴暫存表法 (不使用 CTE，相容舊版)

CREATE PROCEDURE dbo.Get_TreeData
AS
BEGIN
    -- 暫存表記錄節點 ID 與層級
    CREATE TABLE #tmp_table (id INT, lvl INT)

    DECLARE @LevelValue INT = 0
    INSERT INTO #tmp_table SELECT 0, @LevelValue  -- 虛擬根節點

    -- 逐層展開
    WHILE @@ROWCOUNT > 0
    BEGIN
        SET @LevelValue = @LevelValue + 1
        INSERT INTO #tmp_table
        SELECT a.category_id, @LevelValue
        FROM Categories a
        JOIN #tmp_table b ON a.parent_id = b.id AND b.lvl = @LevelValue - 1
    END

    -- 輸出結果：依 sortname 排序即為樹狀順序
    SELECT
        a.category_id,
        a.parent_id,
        a.category_name,
        b.lvl AS [level],
        CASE
            WHEN a.parent_id = 0 THEN a.category_name
            ELSE REPLICATE('-', b.lvl) + a.category_name
        END AS sortname
    FROM Categories a
    JOIN #tmp_table b ON a.category_id = b.id
    ORDER BY sortname

    RETURN
END
GO

3. 執行與結果

EXEC dbo.Get_TreeData

輸出：

category_id  parent_id  category_name  level  sortname
1            0          A              0      A
3            1          AA             1      -AA
4            3          AAA            2      --AAA
8            3          AAA2           2      --AAA2
9            4          AAA3           3      ---AAA3
6            1          AA2            1      -AA2
7            1          AA3            1      -AA3
2            0          B              0      B
5            2          BB             1      -BB
10           2          BB2            1      -BB2

優化方向 (SQL 2005+)

• 改用 遞迴 CTE (Common Table Expression) 一次查詢完成，效能更佳
• 大量資料時，考慮 HierarchyId (SQL 2008+) 或物化路徑 維護排序鍵