SQL Server 左右值編碼實現無限級分類（高效能樹狀結構）

傳統遞歸查詢（WHERE ParentID = @id）在深層級分類下效能極差。MySQL 文件推薦的巢狀集合模型以「左右值編碼」一次查詢即可取得整棵子樹，效能碾壓遞歸與路徑枚舉法。本文示範在 SQL Server 實作完整增刪查存儲程序。

核心原理

每個節點擁有 LID（左值）、RID（右值）、Tree（深度）。規則：

• 子孫節點：LID > 父.LID AND RID < 父.RID
• 祖先節點：LID < 節點.LID AND RID > 節點.RID
• 同層排序：ORDER BY LID 即為前序遍歷順序。

範例：山西 (8,17)，其子節點稷山 (14,15) 滿足 14>8 AND 15<17。

資料表結構

CREATE TABLE CL_ZoneData (
    ID     INT IDENTITY PRIMARY KEY,
    LID    INT NOT NULL,   -- 左值
    RID    INT NOT NULL,   -- 右值
    Tree   INT NOT NULL,   -- 深度（根=1）
    Name   NVARCHAR(50) NOT NULL
);

存儲程序：查詢節點及其所有子孫

CREATE PROCEDURE CLSP_ZoneSelect
    @Root INT,      -- 根節點 ID
    @Tree INT       -- 最大深度（0=不限）
AS
SELECT Z.ID, Z.Tree, Z.Name
FROM CL_ZoneData AS Z, CL_ZoneData AS P
WHERE P.ID = @Root
  AND Z.LID >= P.LID AND Z.RID <= P.RID
  AND (@Tree = 0 OR Z.Tree <= P.Tree + @Tree)
ORDER BY Z.LID ASC;
GO

存儲程序：在指定節點下新增子節點

CREATE PROCEDURE CLSP_ZoneInsert
    @Root INT,           -- 父節點 ID（0=新增根節點）
    @Name NVARCHAR(50),
    @NewID INT OUTPUT
AS
DECLARE @RID INT, @Tree INT;
SET @RID = 1; SET @Tree = 1;

IF @Root = 0
    SELECT TOP 1 @RID = RID + 1 FROM CL_ZoneData ORDER BY RID DESC;
ELSE
    SELECT @RID = RID, @Tree = Tree + 1 FROM CL_ZoneData WHERE ID = @Root;

IF @Root = 0 OR @RID > 1
BEGIN
    UPDATE CL_ZoneData SET RID = RID + 2 WHERE RID >= @RID;
    UPDATE CL_ZoneData SET LID = LID + 2 WHERE LID > @RID;
    INSERT INTO CL_ZoneData (LID, RID, Tree, Name) VALUES (@RID, @RID + 1, @Tree, @Name);
    SET @NewID = SCOPE_IDENTITY();
END
ELSE
    SET @NewID = 0;
GO

存儲程序：刪除節點及其整棵子樹

CREATE PROCEDURE CLSP_ZoneDelete
    @ID INT,
    @DeletedID INT OUTPUT
AS
DECLARE @LID INT, @RID INT, @WID INT, @DID INT;
SET @DID = 0;

SELECT @DID = ID, @LID = LID, @RID = RID, @WID = RID - LID + 1
FROM CL_ZoneData WHERE ID = @ID;

IF @DID != 0
BEGIN
    DELETE FROM CL_ZoneData WHERE LID BETWEEN @LID AND @RID;
    UPDATE CL_ZoneData SET RID = RID - @WID WHERE RID > @RID;
    UPDATE CL_ZoneData SET LID = LID - @WID WHERE LID > @RID;
END
SET @DeletedID = @DID;
GO

優缺點與適用場景

適用：商品分類、地區劃分、組織架構等「讀多寫少、結構相對穩定」的樹狀資料。
不適用：高頻插入/移動的討論串、留言板，建議改用「閉包表」或「物化路徑」。