此问题是要利用 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
