SQL Server使用HierarchyId构建树结构时如何查询所有叶子节点

问题原因 #

1. 原有叶子节点判断逻辑未处理NULL场景：当节点本身没有任何子节点时，你写的SELECT MAX(leaf.Level.GetLevel())子查询会返回NULL，NULL和任何值做比较都会返回UNKNOWN，导致这部分没有子节点的地区节点直接被过滤，所以你拿不到这部分数据。
2. 本地环境与测试环境执行结果不一致的常见排查点：

• 检查本地表中是否存在部分节点CountryISOAlpha2Code字段值和父节点不匹配，导致关联时被排除
• 检查本地Level字段的层级赋值是否存在异常，比如部分地区节点的层级值未按规则写入

最优解决方案 #

叶子节点的核心判断标准是不存在除自身外的其他后代节点，推荐用NOT EXISTS写法实现，执行效率比聚合查询更高，也能避免NULL值问题：

SELECT * 
INTO #parents 
FROM ado.country_subdivisions parents 
WHERE isocode = 'ES' 

SELECT * 
FROM ado.country_subdivisions cs
WHERE EXISTS (
    SELECT 1 FROM #parents p
    WHERE cs.Level.IsDescendantOf(p.Level) = 1
      AND p.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
)
-- 核心叶子节点判断逻辑
AND NOT EXISTS (
    SELECT 1 FROM ado.country_subdivisions leaf
    WHERE leaf.Level.IsDescendantOf(cs.Level) = 1
      AND leaf.Level <> cs.Level -- 排除节点自身
      AND leaf.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
)

原有写法的修复方案 #

如果你希望沿用原有最大层级的判断逻辑，只需要补充NULL值处理即可：

SELECT * 
INTO #parents 
FROM ado.country_subdivisions parents 
WHERE isocode = 'ES' 

SELECT * 
FROM ado.country_subdivisions cs
WHERE EXISTS (
    SELECT 1 FROM #parents p
    WHERE cs.Level.IsDescendantOf(p.Level) = 1
      AND p.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
)
AND ISNULL(
    (SELECT MAX(leaf.Level.GetLevel()) 
     FROM ado.country_subdivisions leaf 
     WHERE leaf.Level.IsDescendantOf(cs.Level) = 1
       AND leaf.Level <> cs.Level),
    -1
) < cs.Level.GetLevel()

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