資料庫使用 HIERARCHYID 紀錄樹狀關聯資料

手上有個案子，資料間有階層性關聯，
原本使用 ParentId 欄位紀錄，隨著樹狀結構變得複雜，
查詢時隨著樹狀結構層數加深，要取得/確認節點間是否屬於同一分支就變得相當麻煩…

後來發現 SQL 提供一種資料型態 HIERARCHYID，
透過編碼紀錄從樹狀目錄根到節點的路徑。
此表示以斜線為開頭，而且只有造訪根目錄的路徑才會以單一斜線表示。
子系之間的比較是透過按照字典順序比較以小數點隔開的整數序列加以執行。
每個層級後面都跟著一個斜線。 因此，斜線會分隔父代與其子系。例如：
  • /
  • /1/
  • /1/2/4
接下來以範例來說明怎麼使用吧!

資料模型 - 分公司銷售量

假設公司分為兩個分公司：台北、新竹，兩公司間的人員僅可檢視自己所在區域的銷售量，但總公司的董事長則可檢視分公司的所有資料。



範例就是這麼簡單；
首先，先來建立相關的資料表：
  1. 先建立部門：
CREATE TABLE Department
(
No HIERARCHYID NOT NULL, // 部門代號，以階層方式記錄
Name NVARCHAR(40) NOT NULL, // 部門名稱，識別用
Level AS No.GetLevel(), // GetLevel()為內建的函式，代表樹狀結構中節點深度，
Path AS CAST(No AS NVARCHAR(100)) //
)
以此結構下，建立各分公司資料：
INSERT INTO Department VALUES(’/1/’, ‘總部’)
INSERT INTO Department VALUES(’/1/1/’, ‘台北’)
INSERT INTO Department VALUES(’/1/1/1/’, ‘信義區’)
INSERT INTO Department VALUES(’/1/1/2/’, ‘內湖區’)
INSERT INTO Department VALUES(’/1/2/’, ‘新竹’)
INSERT INTO Department VALUES(’/1/2/1/’, ‘東區’)
INSERT INTO Department VALUES(’/1/2/2/’, ‘北區’)
  1. 部門銷售量：
CREATE TABLE DepSales
(
Dep HIERARCHYID NOT NULL,
Amount INT
)
銷售量資料：
INSERT INTO DepSales VALUES(’/1/1/1/’, 10000)
INSERT INTO DepSales VALUES(’/1/1/2/’, 100)
INSERT INTO DepSales VALUES(’/1/2/1/’, 2000)
INSERT INTO DepSales VALUES(’/1/2/2/’, 20)
  1. 人員，紀錄所屬部門
CREATE TABLE DepUsers
(
Dep HIERARCHYID NOT NULL,
Name NVARCHAR(200)
)
人員資料：
INSERT INTO DepUsers VALUES(’/1/’, ‘董事長’)
INSERT INTO DepUsers VALUES(’/1/1/’, ‘台北總經理’)
INSERT INTO DepUsers VALUES(’/1/1/2/’, ‘內湖區域經理’)
INSERT INTO DepUsers VALUES(’/1/2/’, ‘新竹總經理’)
INSERT INTO DepUsers VALUES(’/1/2/2/’, ‘新竹北區經理’)

查詢方式

所以，讓我們來查詢看看，新竹總經理可以看到哪些資料?
DECLARE @GroupLevel HIERARCHYID --人員所屬的部門階層代號
SELECT @GroupLevel = Dep FROM DepUsers WHERE Name=‘新竹總經理’

SELECT d.*, ISNULL(s.Amount, 0) AS Amount
FROM Department d
LEFT JOIN DepSales s ON d.No=s.Dep
WHERE d.No.IsDescendantOf(@GroupLevel) = 1
在這裡，使用內建的函式 IsDescendantOf 判斷是否屬於父系之子樹中節點。
(自己也會包含在內)

新竹總經理部門為 /1/2/，紀錄為 0x5B40，所以結果為：








以上範例，如有任何意見歡迎提出討論!

留言

張貼留言