~楓花雪岳~: [SQL] HierarchyId

星期五, 8月 02, 2013

[SQL] HierarchyId - 字串路徑

此論壇問題是要利用 HierarchyId 來找出字串路徑，HierarchyId 可以透過 CAST(HierarchyId AS ncarchar(4000)) 直接轉成字串路徑（EX：\1\2\3），而此問題是要找出以［名稱］為主的字串路徑（EX：公司\課級\組級），在沒有現成函數可以使用的前提下，還是只能跑 CTE 來取得。

Sample Data

USE TempDB
GO

IF OBJECT_ID('Demo') IS NOT NULL
    DROP TABLE Demo

CREATE TABLE Demo 
(
    NO int NOT NULL PRIMARY KEY NONCLUSTERED ,
    NOName nvarchar(500) , 
    HID hierarchyid ,
    HLevel AS HID.GetLevel()
)
GO 

DECLARE @Parent AS HIERARCHYID
DECLARE @Child AS HIERARCHYID

-- 公司資料
SELECT @Parent = hierarchyid::GetRoot()
INSERT INTO Demo (NO,NOName,HID) VALUES(1,N'公司',@Parent)

-- 課級資料
SELECT @Parent = HID FROM Demo WHERE NO = 1
SELECT @Child = @parent.GetDescendant(NULL, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(2,N'課級1',@Child)
SELECT @Child = @parent.GetDescendant(@Child, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(3,N'課級2',@Child)

-- 組級資料
SELECT @Parent = HID FROM Demo WHERE NO = 2
SELECT @Child = @parent.GetDescendant(NULL, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(4,'組級1',@Child)
SELECT @Child = @parent.GetDescendant(@Child, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(5,'組級2',@Child)

SELECT @Parent = HID FROM Demo WHERE NO = 3
SELECT @Child = @parent.GetDescendant(NULL, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(6,'組級3',@Child)
SELECT @Child = @parent.GetDescendant(@Child, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(7,'組級4',@Child)
SELECT @Child = @parent.GetDescendant(@Child, NULL)
INSERT INTO Demo (NO,NOName,HID) VALUES(8,'組級5',@Child)

CTE 求名稱字串路徑

; 
WITH CTE
AS
(
  SELECT 
      NO , 
      HID AS ChildHID ,
      HID.GetAncestor(1) AS ParentHID ,                    -- 說明 1
      HLevel ,
      NOName AS ChildName , 
      CAST(NOName AS varchar(100)) AS NamePath ,
      CAST(HID AS nvarchar(4000)) AS HPath                 -- 說明 2
  FROM Demo
  WHERE HID.GetAncestor(1) IS NULL                         -- 說明 1
  UNION ALL
  SELECT 
      D.NO ,
      D.HID ,
      D.HID.GetAncestor(1) ,
      D.HLevel ,
      D.NOName ,                                      
      CAST(NamePath + '\' + D.NOName AS varchar(100)),
      CAST(D.HID AS nvarchar(4000))
  FROM CTE AS T 
      JOIN Demo AS D ON T.ChildHID = D.HID.GetAncestor(1)  -- 說明 1
)
SELECT
    NO ,
    ChildHID ,
    ParentHID ,
    HLevel ,
    -- 說明3
    REPLICATE(SPACE(1) , HLevel * 10) + ChildName AS NameTree , 
    NamePath ,
    HPath
FROM CTE
ORDER BY HPath

說明 1：利用 GetAncestor(1) 來找出父節點，沒有父節點會利用 NULL 來表示
說明 2：CAST(HierarchyId AS ncarchar(4000)) 可以找出 HierarchyId Path
說明 3：故意同時秀出 NameTree、NamePath 和 HPath 這三個欄位資訊。NamePath 需要靠 CTE 跑出來、NameTree 和 HPath 只需要利用 HierarchyId 就可以找出來

下述 T-SQL 結果，同下方結果圖示，但沒有 NamePath 欄位資訊而以

SELECT
    NO ,
    HID AS ChildHID ,
    HID.GetAncestor(1) AS ParentHID ,
    HLevel ,
    REPLICATE(SPACE(1),HLevel*10) + NOName AS NameTree,
    CAST(HID AS NVARCHAR(4000)) AS HPath
FROM Demo
ORDER BY HPath