SQL Server 自動維護路徑的無限級分類表設計

問題背景

傳統無限級分類表僅含 Id, ParentId, Name 三欄，查詢某節點所有子孫需遞迴或多次查詢，效能低且程式複雜。解決方案：新增路徑欄位，記錄從根節點到當前節點的完整路徑，查詢子孫變為簡單的 LIKE 'path/%' 操作。

表結構設計

CREATE TABLE [Sort] (
    [Id] INT PRIMARY KEY,
    [ParentId] INT NULL,
    [Name] NVARCHAR(500),
    [IdPath] VARCHAR(500),   -- 如 '/1/3/7/'
    [NamePath] NVARCHAR(500) -- 如 '/根目錄/子目錄/孫目錄/'
);

核心邏輯：存儲過程自動維護路徑

建立 SetSortPath 存儲過程，接受 @Id 參數，向上遞迴父節點構建路徑：

CREATE PROCEDURE [dbo].[SetSortPath]
    @Id INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @PId VARCHAR(10), @ParentId INT, @Name NVARCHAR(500);
    DECLARE @IdPath VARCHAR(500) = '/', @NamePath NVARCHAR(500) = '/';

    SELECT @PId = [ParentId] FROM [Sort] WHERE [Id] = @Id;

    WHILE (@PId IS NOT NULL)
    BEGIN
        SELECT @ParentId = [ParentId], @Name = [Name] FROM [Sort] WHERE [Id] = @PId;
        SET @IdPath = '/' + @PId + @IdPath;
        SET @NamePath = '/' + @Name + @NamePath;
        IF (@ParentId <> @PId) SET @PId = @ParentId;
        ELSE SET @PId = NULL; -- 防止循環引用
    END

    IF (@IdPath = '/')
        UPDATE [Sort] SET [IdPath] = NULL, [NamePath] = NULL WHERE [Id] = @Id;
    ELSE
        UPDATE [Sort] SET [IdPath] = @IdPath, [NamePath] = @NamePath WHERE [Id] = @Id;
END

觸發器自動化

在 Sort 表上建立 INSERT, UPDATE, DELETE 觸發器，呼叫 SetSortPath：

• 新增/修改節點時，更新該節點及其所有後代路徑。
• 刪除節點時，清理相關路徑或標記失效。

查詢範例

-- 取得 Id=3 節點的所有子孫
SELECT * FROM [Sort] WHERE [IdPath] LIKE '/3/%';

-- 取得完整路徑顯示
SELECT [NamePath] FROM [Sort] WHERE [Id] = 7;
-- 結果: /根目錄/子目錄/孫目錄/

優缺點權衡

結語

路徑枚舉模式適合「讀多寫少」的分類場景。若寫入頻繁，可考慮 Closure Table（關係閉包表）或遞迴 CTE 方案。