[SQL] HierarchyID - 批次

在 [SQL] HierarchyID - CRUD 內有介紹 insert、update、delete，但都是根據單一節點進行處理，該筆記紀錄多節點處理

環境建置

使用 Table 說明

• tblCompany Table Schema 為紀錄上一層 (父子) 架構
• tblHID Table Schema 為 HierarchyID 架構，以最單純方式來建立，直接以 HierarchyID 當成 PK，方便記錄 CRUD

DROP TABLE IF EXISTS tblCompany

CREATE TABLE tblCompany
(
	DepID int PRIMARY KEY ,
	DepName nvarchar(20) ,
	ParentDepID int
)

INSERT INTO tblCompany VALUES(1 , N'公司名稱' , NULL)
INSERT INTO tblCompany VALUES(2 , N'業務' , 1)
INSERT INTO tblCompany VALUES(3 , N'財務' , 1)
INSERT INTO tblCompany VALUES(4 , N'廠務 1' , 1)
INSERT INTO tblCompany VALUES(5 , N'倉庫 1' , 4)
INSERT INTO tblCompany VALUES(6 , N'工作站 1' , 4)
INSERT INTO tblCompany VALUES(7 , N'產線 1-1' , 6)
INSERT INTO tblCompany VALUES(8 , N'產線 1-2' , 6)
INSERT INTO tblCompany VALUES(9 , N'廠務 2' , 1)
INSERT INTO tblCompany VALUES(10 , N'工作站 2' , 9)
INSERT INTO tblCompany VALUES(11 , N'產線 2-1' , 10)
INSERT INTO tblCompany VALUES(12 , N'產線 2-2' , 10)
INSERT INTO tblCompany VALUES(13 , N'人事' , 1)

DROP TABLE IF EXISTS tblHID

CREATE TABLE tblHID  
( 
	HID HierarchyID PRIMARY KEY CLUSTERED ,  
	DepID int UNIQUE NOT NULL,  
	DepName nvarchar(20) NOT NULL
) ;  

文章內容統一以該語法來進行查詢操作後結果，就不在反覆寫出

SELECT 
	HID , 
	HID.ToString() AS HPath ,   
	HID.GetLevel() AS HLevel ,
	DepID, 
	CAST(REPLICATE(SPACE(1) , HID.GetLevel() * 10) + DepName as nvarchar(4000)) AS NameTree
FROM tblHID ;

   

INSERT

批次新增是參考官方文章 - 將資料表轉換為階層式結構，把 紀錄上一層 (父子)  架構轉換為 HierarchyID 架構，在 [SQL] HierarchyID - 資料型態 內有提到

HierarchyID 需以斜線符號 (/) 開頭 (樹狀圖根結點 root)，也必須以斜線符號 (/)  結尾，中間使用數字 (int、decimal) 標示各節點，例如：/1/2.1/3，以 binary 來儲存

該範例就是以特性，先整理好該規則路徑並進行新增

下述為轉換 TSQL 語法，分為兩部分

• cteRowNO：使用 Row_Number() 針對 ParentDepID 進行編號
• cteHID：使用 CTE 遞迴完整呈現樹狀圖並組合 HierarchyID

; 
WITH cteRowNO AS
(
    SELECT 
        DepID ,
        DepName ,
        ParentDepID ,
        -- 進行資料編號
        ROW_NUMBER() OVER (PARTITION BY ParentDepID ORDER BY ParentDepID) AS RowNO
    FROM tblCompany 
)
,
cteHID AS
(
    SELECT
        DepID , 
        DepName ,
        ParentDepID ,
        RowNO ,
        CAST(0 as int) AS TreeLevel ,
        CAST(DepName as nvarchar(4000)) AS TreePath ,
        -- 串接 HierarchyID
        hierarchyid::GetRoot() AS HID
    FROM cteRowNO
    WHERE ParentDepID IS NULL
    UNION ALL
    SELECT
        C.DepID , 
        C.DepName , 
        C.ParentDepID ,
        C.RowNO ,
        CAST(TreeLevel + 1 as int) AS TreeLevel ,
        CAST(TreePath + '_' + C.DepName as nvarchar(4000)) AS TreePath ,
        -- 串接 HierarchyID
        CAST(T.HID.ToString() + CAST(C.RowNO AS varchar(30)) + '/' AS hierarchyid) AS HID
    FROM cteHID AS T
        JOIN cteRowNO AS C ON T.DepID = C.ParentDepID
)
INSERT INTO tblHID (HID , DepID , DepName)
SELECT HID , DepID , DepName
FROM cteHID

IsDescendantOf()

 
判斷節點是否為該節點的子節點且包含節點本身，使用語法為

child.IsDescendantOf(parent)

語法分析為判斷 child 是否為 parent 的子結點，是的話回傳 true

簡易範例，要注意有包含 /4/1/ 該節點

 -- '/4/1/' 為 DepID = 10 (廠務 2)
SELECT * ,
	HID.ToString() AS HIDPath
FROM tblHID
WHERE HID.IsDescendantOf('/4/1/') = 1;
-- OR
WHERE HID.IsDescendantOf(hierarchyid::Parse('/4/1/')) = 1;

UPDATE

以轉換後資料為基礎，要把 [廠務 2 和其子節點] 搬移至 [廠務 1] 去

完整 update 語法

DECLARE @oldParent hierarchyid, @newParent hierarchyid , @lastChildHID hierarchyid  , @newHID hierarchyid

-- DepID = 10 的 HID.ToString() = '/4/1/'
SELECT @oldParent = HID FROM tblHID WHERE DepID = 10

-- DepID = 4 的 HID.ToString() = '/3/'
SELECT @newParent = HID FROM tblHID WHERE DepID = 4

-- 取得新父節點下最大子節點
SELECT @lastChildHID  = MAX(HID) FROM tblHID WHERE HID.GetAncestor(1) = @newParent ; 

-- 取得 [新父節點] 下大於 [最大子節點] 的子節點
SELECT @newHID = @newParent.GetDescendant(@lastChildHID, NULL) 

-- update 前先透過 select 確認 HID
SELECT * , 
    HID.GetReparentedValue
    (
        @oldParent, 
        @newHID
    ).ToString() AS NewHierarchyString
FROM tblHID
WHERE HID.IsDescendantOf(@oldParent) = 1;

-- 實際進行 update 
UPDATE tblHID
SET HID = HID.GetReparentedValue
    (
        @oldParent, 
        @newHID
    )
WHERE HID.IsDescendantOf(@oldParent) = 1;

重點一：取得 [新父節點] 下大於 [最大子節點] 的子節點

想像成取得最大流水號後加 1 的流程就是

-- 取得新父節點下最大子節點
SELECT @lastChildHID  = MAX(HID) FROM tblHID WHERE HID.GetAncestor(1) = @newParent ;  

-- 取得 [新父節點] 下大於 [最大子節點] 的子節點
SELECT @newHID = @newParent.GetDescendant(@lastChildHID, NULL)   

重點二：GetReparentedValue() 應用

update 是透過 GetreparentedValue() 進行，也可以應用在 select 內先觀察新 HID 是否正確

-- update 前先透過 select 確認 HID
SELECT * , 
    HID.GetReparentedValue
    (
        @oldParent, 
        @newHID
    ).ToString() AS NewHierarchyString
FROM tblHID
WHERE HID.IsDescendantOf(@oldParent) = 1;

DELETE

在 [SQL] HierarchyID - CRUD Delete 介紹內提到

要特別注意刪除節點時，該節點是否還有子節點，萬一還有子節點情況下，子節點會變成孤兒節點

因此該範例 delete 時會搭配 IsDescendantOf() 連子結點一併刪除，避免孤兒節點存在

-- DepID = 9 (廠務 2) 的 HID.ToString() = /4/
--		其下子節點已經移轉至 (廠務 1) 去，不用擔心會有孤兒節點
DELETE FROM tblHID WHERe DepID = 9 

DECLARE @HID hierarchyid
-- DepID = 6 (工作站 1) 的 HID.ToString() = /3/2/
SELECT @HID = HID FROM tblHID WHERE DepID = 6

-- 使用 IsDescendantOf() 來篩選節點和其子結點
DELETE FROM tblHID WHERE HID.IsDescendantOf(@HID) = 1

• 參考資料
• IsDescendantOf()、GetAncestor()、GetDescendant()、GetReparentedValue()