如何按最右侧节点查询hierarchyid数据类型

嘿，刚上手hierarchyid是吧？我当初第一次折腾这个类型的时候，也对着二进制路径懵了好一阵，咱们来一步步捋捋你的设计，看看怎么优化适配人员层级的场景～

首先得夸一句：你的初始思路已经抓对了核心——用hierarchyid存储层级路径，再加个计算列sPath转成可读的字符串格式，这点太重要了，毕竟直接看hierarchyid的二进制值跟看天书似的。

先聊聊你当前设计可以补全的点 #

你提到Id是层级中最右侧的节点，这里需要明确：如果Id是每个员工的唯一标识，那最好让hPath的最后一个节点值和Id绑定，这样路径和实体的对应关系才不会乱。比如老板的Id=1，路径就是/1/；他的下属Id=2，路径就是/1/2/，后续生成子节点时能直接关联上员工ID。

我给你调整了表结构，补上常用业务字段和实用的计算列：

create table #WithId (
    Id int primary key clustered,
    Name nvarchar(50) not null, -- 员工姓名
    JobTitle nvarchar(50) null, -- 职位
    hPath hierarchyid not null,
    sPath as hPath.ToString() persisted, -- 标记persisted可以给这个列建索引
    Level as hPath.GetLevel() persisted -- 快速获取层级深度，比如根节点是0，一级下属是1
)

再给你几个常用的层级操作示例 #

这些是日常维护人员层级时高频用到的操作，直接抄就能用：

1. 添加根节点（比如公司老板）

insert into #WithId(Id, Name, JobTitle, hPath)
values(1, '王总', 'CEO', hierarchyid::GetRoot())

2. 给指定上级添加下属

-- 给Id=1的王总加个技术总监下属
declare @parentPath hierarchyid
select @parentPath = hPath from #WithId where Id=1

insert into #WithId(Id, Name, JobTitle, hPath)
values(2, '张三', '技术总监', @parentPath.GetDescendant(null, null))

3. 查询某个节点的所有下属（含子节点、孙节点）

-- 查王总的所有下属
declare @targetPath hierarchyid
select @targetPath = hPath from #WithId where Id=1

select * from #WithId 
where hPath.IsDescendantOf(@targetPath) = 1 
and hPath != @targetPath -- 排除自身

4. 查询某个员工的完整上级路径

-- 查Id=2的张三的所有上级
select * from #WithId 
where hPath in (
    select hPath.GetAncestor(n) 
    from #WithId 
    cross join (select top (select hPath.GetLevel() from #WithId where Id=2) row_number() over(order by (select null)) n from sys.all_columns) levels
    where Id=2
)
order by hPath

5. 移动节点（把员工从一个部门转到另一个部门）

-- 把张三从王总下属转到Id=3的李总下属
declare @oldParent hierarchyid, @newParent hierarchyid, @targetPath hierarchyid
select @oldParent = hPath from #WithId where Id=1
select @newParent = hPath from #WithId where Id=3
select @targetPath = hPath from #WithId where Id=2

update #WithId 
set hPath = @targetPath.GetReparentedValue(@oldParent, @newParent)
where Id=2

最后提两个优化建议 #

• 给hPath加个唯一约束：避免出现重复路径的异常情况，比如alter table #WithId add constraint UQ_WithId_hPath unique(hPath)
• 给hPath建非聚集索引：层级查询（找下属、找上级）都是基于路径的，索引能大幅提升查询速度：create nonclustered index IX_WithId_hPath on #WithId(hPath)

内容的提问来源于stack exchange，提问作者Xedni