[SQL] HierarchyID - 分支前兩筆資料

在 Line 社群看見的問題，要找到樹狀圖上該條件分支上的前兩筆資料並轉置呈顯，原問題是採取父子階層，也把資料轉為 HierarchyID 來練習看看，把問題簡化為下面表格

 

父子階層

直接參考其他筆記

• [Challenge] 找出從屬關係
• [Challenge] 組織圖
• [SQL] PIVOT 和 UNPIVOT
• [SQL] 字串欄位轉置

USE AdventureWorks2022
GO

DROP TABLE IF EXISTS tblParentChild

CREATE TABLE tblParentChild
(
	[id]        INT PRIMARY KEY NOT NULL,
    [parent_id] INT,
    [name]      NVARCHAR(50)
)

INSERT INTO tblParentChild([id], [parent_id], [name]) VALUES
	(0  , NULL , 'Root'),
	(1  , 0    , 'A'),
	(2  , 1    , 'A1'),
	(4  , 0    , 'B'),
	(5  , 4    , 'B1'),
	(6  , 5    , 'B12'),
	(7  , 6    , 'B123'),
	(8  , 0    , 'C'),
	(9  , 8    , 'C1'),
	(10 , 9    , 'C12'),
	(11 , 10   , 'C123'),
	(12 , 11   , 'C1234'),
	(20 , 0    , 'D');

;
WITH CTE AS 
(
		SELECT 
			id AS GroupID ,
			id ,
			[name] ,
			1 AS [level] ,
			parent_id
		FROM tblParentChild
		WHERE [name] IN ('A1', 'B12', 'C1234', 'D')
		UNION ALL
		SELECT T.GroupID ,
		       PC.id ,
		       PC.[name] ,
		       T.[level] + 1 AS [level],
		       [PC].[parent_id]				
		FROM cte AS T
			JOIN tblParentChild AS PC ON [T].[parent_id] = [PC].[id]
		WHERE PC.parent_id IS NOT NULL
)
SELECT 
     T2.* ,
	 T1.[1] AS [top_level_id] ,
     T1.[2] AS [top2_level_id]
FROM
	(
	   SELECT PV.*
	   FROM	
			(
			   SELECT 
					GroupID  ,
					id ,
					ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY [level] DESC) AS RowNO
			   FROM CTE
			) AS P
			PIVOT
			(
				MAX(id) FOR RowNO  IN ([1] , [2])
			) AS PV
	) AS T1
	JOIN tblParentChild AS T2 ON T1.GroupID = t2.id

HierarchyID

先把父子階層資料轉換為 HierarchyID，參考 [SQL] HierarchyID - 批次 insert 內容

DROP TABLE IF EXISTS tblHID

CREATE TABLE tblHID  
( 
	HID HierarchyID PRIMARY KEY CLUSTERED ,  
	ID int UNIQUE NOT NULL,  
	Name nvarchar(100) NOT NULL
) ;  

; 
WITH cteRowNO AS
(
    SELECT 
        ID ,
        Name ,
        Parent_ID ,
        -- 進行資料編號
        ROW_NUMBER() OVER (PARTITION BY Parent_ID ORDER BY Parent_ID) AS RowNO
    FROM tblParentChild
)
,
cteHID AS
(
    SELECT
        ID , 
        Name ,
        Parent_ID ,
        RowNO ,
        CAST(0 as int) AS TreeLevel ,
        CAST(Name as nvarchar(4000)) AS TreePath ,
        -- 串接 HierarchyID
        hierarchyid::GetRoot() AS HID
    FROM cteRowNO
    WHERE Parent_ID IS NULL
    UNION ALL
    SELECT
        C.ID , 
        C.Name , 
        C.Parent_ID ,
        C.RowNO ,
        CAST(TreeLevel + 1 as int) AS TreeLevel ,
        CAST(TreePath + '_' + C.Name 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.ID = C.Parent_ID
)
INSERT INTO tblHID (HID , ID , Name)
SELECT HID , ID , Name
FROM cteHID

實際執行 TSQL 

SELECT 
	T3.* ,
	PV.[1] AS [top_level_id], 
	PV.[2] AS [top2_level_id]
FROM 
	(
		SELECT 			
			T1.ID AS ParentID,
			T2.ID ,
			T2.HID.GetLevel() AS [Level] 
		FROM tblHID AS T1
                        -- 重點條件
			JOIN tblHID AS T2 ON T2.HID.IsDescendantOf(T1.HID.GetAncestor(T1.HID.GetLevel() - 1)) = 1
								AND T2.HID.GetLevel() IN ( 1 , 2 )
		WHERE T1.[name] IN ('A1', 'B12', 'C1234', 'D')
	) AS P
	PIVOT
	(
		MAX(ID) FOR [Level] IN ([1] , [2])
	) AS PV
	JOIN tblHID AS T3 ON PV.ParentID = T3.ID
ORDER BY ParentID

重點條件解析，從下述獨立語法可以找出該條件最上層 top1_levle 節點

SELECT * ,
	HID.GetAncestor(HID.GetLevel() - 1) AS top1_level
FROM tblHID 
WHERE ID > 0
ORDER BY ID

• 延伸閱讀
• [SQL] APPLY
• [Challenge] 找出各部門第二高薪人員