SQL hierarchyID如何获取最低层子节点（最后一级节点）的值？

结论 #

没有直接的内置方法可以一步获取HierarchyID类型指定层级的节点值，但可以通过官方自带的基础方法组合实现需求，实现逻辑简单可靠。 #

获取路径最后一级节点值 #

你需要的获取最后一级节点值的实现代码如下，假设存储HierarchyID的字段名为node_path，表名为hierarchy_data：

SELECT
  node_path.ToString() AS full_path,
  -- 根节点层级为0，无对应最后一级值，返回NULL
  CASE WHEN node_path.GetLevel() = 0 THEN NULL
  -- 方法：字符串处理，直观兼容所有场景
  ELSE PARSENAME(REPLACE(RTRIM(node_path.ToString(), '/'), '/', '.'), 1)
  END AS last_level_value
FROM hierarchy_data

针对你的示例数据运行结果如下： #

获取任意指定层级节点值 #

如果需要获取任意层级的节点值，可以用如下通用写法，调整@target_level的数值即可获取对应层级的值（根节点为0级）：

DECLARE @target_level INT = 2; -- 可自定义需要获取的层级
SELECT
  node_path.ToString() AS full_path,
  CASE WHEN node_path.GetLevel() < @target_level THEN NULL
  ELSE PARSENAME(REPLACE(RTRIM(node_path.ToString(), '/'), '/', '.'), node_path.GetLevel() - @target_level + 1)
  END AS target_level_value
FROM hierarchy_data

之前方案失败原因说明 #

• GetDescendant方法的作用是生成当前节点的子节点对象，并非用来提取已有节点的层级值，不符合使用场景
• GetAncestor方法仅支持非负整数入参，传入负数会触发参数校验报错，无法实现倒序取节点的效果

内容的提问来源于stack exchange，提问作者Y.G.J