第 2 課:在階層式資料表中建立與管理資料
適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體
在第 1 課中,您修改了現有的資料表以使用 hierarchyid 資料類型,並使用現有資料的表示法來擴展 hierarchyid 資料行。 在本課程中,您將從新資料表開始,然後使用階層式方法插入資料。 接著,您將使用階層式方法來查詢與操作資料。
必要條件
若要完成本教學課程,您需要 SQL Server Management Studio、執行 SQL Server 伺服器的存取權,以及 AdventureWorks 資料庫。
如需在 SSMS 中還原資料庫的指示,請參閱:還原資料庫。
使用 hierarchyid 資料類型建立資料表
以下範例會建立名稱為 EmployeeOrg 的資料表,其中同時包含員工資料及其回報的階層。 本範例會在 AdventureWorks2022
資料庫中建立資料表,但這是選擇性的。 為了要維持此範例的簡單性,此資料表僅包含五個資料行:
- OrgNode 是一個 hierarchyid 資料行,其中儲存階層式關聯性。
- OrgLevel 是一個以 OrgNode 資料行為基礎的計算資料行,後者可將每個節點層級儲存在階層中。 它將會用於廣度優先的索引。
- EmployeeID 包含用於應用程式 (例如,薪資) 的一般員工識別碼。 在開發新應用程式時,這些應用程式可以使用 OrgNode 資料行,因此不需要另外的這個 EmployeeID 資料行。
- EmpName 包含員工的名稱。
- Title 包含員工的職稱。
建立 EmployeeOrg 資料表
在 [查詢編輯器] 視窗中,執行下列程式碼來建立
EmployeeOrg
資料表。 指定OrgNode
資料行做為具有叢集索引的主要索引鍵將會建立深度優先的索引:USE AdventureWorks2022; GO if OBJECT_ID('HumanResources.EmployeeOrg') is not null drop table HumanResources.EmployeeOrg CREATE TABLE HumanResources.EmployeeOrg ( OrgNode hierarchyid PRIMARY KEY CLUSTERED, OrgLevel AS OrgNode.GetLevel(), EmployeeID int UNIQUE NOT NULL, EmpName varchar(20) NOT NULL, Title varchar(20) NULL ) ; GO
執行下列程式碼,在
OrgLevel
和OrgNode
資料行上建立複合式索引,以支援有效的廣度優先搜尋:CREATE UNIQUE INDEX EmployeeOrgNc1 ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ; GO
資料表現在已經準備好可以填寫資料。 下一個工作將是使用階層式方法擴展資料表。
使用階層式方法填入階層式資料表
AdventureWorks2022 行銷部門有 8 名員工。 員工層級如下:
David( EmployeeID 6) 是行銷經理。 David管理三名行銷專員:
Sariya( EmployeeID 46)
John( EmployeeID 271)
Jill( EmployeeID 119)
Sariya 管理行銷助理Wanida ( EmployeeID269), John 管理行銷助理Mary ( EmployeeID272)。
插入階層樹狀結構的根目錄
下列範例會將行銷經理 David 插入階層根目錄的資料表中。 OrdLevel 資料行為計算資料行。 因此,不是 INSERT 陳述式的一部分。 第一筆記錄使用 GetRoot() 方法,將這個第一筆記錄擴展為階層的根目錄。
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ; GO
執行下列節點以檢查資料表中的初始資料列:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ;
以下為結果集。
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager
如同上一個課程,我們使用 ToString()
方法,將 hierarchyid 資料類型轉換為比較容易了解的格式。
插入從屬員工
David 管理 Sariya。 若要插入 Sariya 的節點,您必須建立適合 hierarchyid 資料類型的 OrgNode值。 下列程式碼會建立 hierarchyid 資料類型的變數,並使用資料表的 OrgNode 根目錄值擴展。 然後,搭配 GetDescendant() 方法使用該變數來插入從屬節點的資料列。
GetDescendant
會採用兩個引數。 檢閱下列引數值的選項:- 如果父系為 NULL,
GetDescendant
會傳回 NULL。 - 如果父系不是 NULL,而 child1 和 child2 都是 NULL,
GetDescendant
則會傳回父系的一個子系。 - 如果父系和 child1 都不是 NULL,而 child2 是 NULL,
GetDescendant
會傳回父系中大於 child1 的子系。 - 如果父系和 child2 不是 NULL,而 child1 是 NULL,
GetDescendant
會傳回父系中小於 child2 的子系。 - 如果父系、child1 和 child2 都不是 NULL,
GetDescendant
會傳回父系中大於 child1 且小於 child2 的子系。
下列程式碼使用根目錄父系的
(NULL, NULL)
引數,因為除了根目錄之外,資料表中還沒有任何資料列。 執行下列程式碼以插入 Sariya:DECLARE @Manager hierarchyid SELECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeOrg ; INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ;
- 如果父系為 NULL,
從第一個程序開始重複查詢,以查詢資料表並查看項目如何出現:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ;
以下為結果集。
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager /1/ 0x58 1 46 Sariya Marketing Specialist
建立輸入新節點的程序
若要簡化輸入資料的方式,請建立下列預存程序,將員工新增到 EmployeeOrg 資料表中。 該程序會接受要新增之員工的輸入值。 這包括新員工之主管的 EmployeeID 、新員工的 EmployeeID 號碼,及其姓氏和職稱。 此程序會使用
GetDescendant()
以及 GetAncestor() 方法。 執行下列程式碼以建立程序:CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) AS BEGIN DECLARE @mOrgNode hierarchyid, @lc hierarchyid SELECT @mOrgNode = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = @mgrid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @lc = max(OrgNode) FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) =@mOrgNode ; INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title) COMMIT END ; GO
下列範例會加入 David直接或間接管理的其餘 4 名員工。
EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ; EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ; EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ; EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;
再次執行下列查詢,檢查 EmployeeOrg 資料表中的資料列:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ; GO
以下為結果集。
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager /1/ 0x58 1 46 Sariya Marketing Specialist /1/1/ 0x5AC0 2 269 Wanida Marketing Assistant /2/ 0x68 1 271 John Marketing Specialist /2/1/ 0x6AC0 2 272 Mary Marketing Assistant /3/ 0x78 1 119 Jill Marketing Specialist
資料表現在已經完全擴展到行銷組織了。
使用階層方法查詢階層式資料表
既然 HumanResources.EmployeeOrg 資料表會完全擴展,此工作將會顯示如何使用某些階層式方法查詢階層。
尋找從屬節點
Sariya 有一個從屬員工。 若要查詢 Sariya 的部屬,請執行使用 IsDescendantOf 方法的下列查詢:
DECLARE @CurrentEmployee hierarchyid SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 46 ; SELECT * FROM HumanResources.EmployeeOrg WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1 ;
結果會同時列出 Sariya 和 Wanida。 Sariya 是層級 0 的下階,因此會被列出。 Wanida 是層級 1 的下階。
您也可以使用 GetAncestor 方法來查詢這項資訊。
GetAncestor
會針對您嘗試傳回的層級傳回一個引數。 由於 Wanida 是 Sariya 下的一個層級,請按照下列程式碼的示範,使用GetAncestor(1)
:DECLARE @CurrentEmployee hierarchyid SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 46 ; SELECT OrgNode.ToString() AS Text_OrgNode, * FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) = @CurrentEmployee
這次結果列出只 Wanida。
現在,將
@CurrentEmployee
變更為 David (EmployeeID 6),並將層級變更為 2。 執行下列程式碼也可以傳回 Wanida:DECLARE @CurrentEmployee hierarchyid SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 6 ; SELECT OrgNode.ToString() AS Text_OrgNode, * FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(2) = @CurrentEmployee
這次,您也可以接收到兩個層級下的 Mary,她也是回報給 David。
使用 GetRoot 與 GetLevel
階層成長得越大時,判斷成員在階層中的位置時,也就變得更加困難。 使用 GetLevel 方法,尋找階層中,每個資料列下有多少層級。 執行下列程式碼以檢視所有資料列的層級:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode.GetLevel() AS EmpLevel, * FROM HumanResources.EmployeeOrg ; GO
使用 GetRoot 方法,尋找階層中的根節點。 下列程式碼範例會傳回根目錄的單一資料列:
SELECT OrgNode.ToString() AS Text_OrgNode, * FROM HumanResources.EmployeeOrg WHERE OrgNode = hierarchyid::GetRoot() ; GO
使用階層式方法重新排列階層式資料表中的資料順序
適用於:SQL Server
重新組織階層是常見的維護工作。 在這項工作中,我們將會使用 UPDATE 陳述式搭配 GetReparentedValue 方法,先將單一資料列移到階層中的新位置。 然後,我們會將整個子樹移到新位置。
GetReparentedValue
方法會使用兩個引數。 第一個引數描述要修改的階層部分。 例如,如果階層為 /1/4/2/3/ 而您想要變更 /1/4/ 區段,讓該階層變成 /2/1/2/3/,留下最後兩個節點 (2/3/) 不變,您必須提供變更的節點 (/1/4/) 作為第一個引數。 第二個引數會提供新的階層層級,在範例中為 /2/1/。 兩個引數不必包含相同的層級數目。
將單一資料列移到階層中的新位置
目前 Wanida 回報給 Sariya。 在此程序中,您會從目前的節點 /1/1/, 移動 Wanida,因此她會回報給 Jill。 她的新節點將會變成 /3/1/ ,因此 /1/ 是第一個引數,而 /3/ 是第二個引數。 這些引數會對應到 Sariya 和 Jill 的 OrgNode 值。 執行下列程式碼,將 Wanida 從 Sariya 的組織移到 Jill 的組織:
DECLARE @CurrentEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 269 ; SELECT @OldParent = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 46 ; SELECT @NewParent = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 119 ; UPDATE HumanResources.EmployeeOrg SET OrgNode = @CurrentEmployee. GetReparentedValue(@OldParent, @NewParent) WHERE OrgNode = @CurrentEmployee ; GO
執行下列程式碼以查看結果:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ; GO
Wanida 現在是在節點 /3/1/。
重新組織階層的區段
為示範如何同時移動大量的人員,請先執行下列程式碼,以便加一位實習生回報給 Wanida:
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern' ; GO
現在,Kevin 回報給 Wanida,Wanida 回報給 Jill,而 Jill 回報給 David。 也就是說,Kevin 位於層級 /3/1/1/。 若要將 Jill 的所有部屬移到新的主管之下,我們會將讓 /3/ 當作其 OrgNode 的所有節點更新為新值。 執行下列程式碼,將 Wanida 更新為回報給 Sariya,但繼續讓 Kevin 回報給 Wanida:
DECLARE @OldParent hierarchyid, @NewParent hierarchyid SELECT @OldParent = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 119 ; -- Jill SELECT @NewParent = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 46 ; -- Sariya DECLARE children_cursor CURSOR FOR SELECT OrgNode FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) = @OldParent; DECLARE @ChildId hierarchyid; OPEN children_cursor FETCH NEXT FROM children_cursor INTO @ChildId; WHILE @@FETCH_STATUS = 0 BEGIN START: DECLARE @NewId hierarchyid; SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL) FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) = @NewParent; UPDATE HumanResources.EmployeeOrg SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId) WHERE OrgNode.IsDescendantOf(@ChildId) = 1; IF @@error <> 0 GOTO START -- On error, retry FETCH NEXT FROM children_cursor INTO @ChildId; END CLOSE children_cursor; DEALLOCATE children_cursor;
執行下列程式碼以查看結果:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ; GO
以下為結果集。
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/ Ox 0 6 David Marketing Manager
/1/ 0x58 1 46 Sariya Marketing Specialist
/1/1/ 0x5AC0 2 269 Wanida Marketing Assistant
/1/1/1/ 0x5AD0 3 291 Kevin Marketing Intern
/2/ 0x68 1 271 John Marketing Specialist
/2/1/ 0x6AC0 2 272 Mary Marketing Assistant
/3/ 0x78 1 119 Jill Marketing Specialist
曾經回報給 Jill (Wanida 和 Kevin) 的整個組織樹狀結構現在會回報給 Sariya。
若要讓預存程序辨識階層的區段,請參閱 移動子樹的<移動子樹>一節。
意見反映
https://aka.ms/ContentUserFeedback。
即將推出:我們會在 2024 年淘汰 GitHub 問題,並以全新的意見反應系統取代並作為內容意見反應的渠道。 如需更多資訊,請參閱:提交及檢視以下的意見反映: