SQL Server hierarchyid层级树中查询满足边界条件的最低层级节点

核心逻辑梳理 #

你需要的结果本质是为每个最低层级（省份级）的行政单元，向上查找其祖先链路中最近的存在边界的节点，将所有找到的节点去重后返回。该逻辑完全覆盖你给出的四类场景：

• 省份自身有边界：直接返回该省份
• 省份无边界、所属大区有边界：返回所属大区
• 省份无边界、所属大区也无边界：返回国家层级节点

调整后查询语句 #

WITH BottomLevelNodes AS (
    -- 先获取所有最低层级（省份级）的节点
    SELECT IsoCode, Level, CountryISOAlpha2Code
    FROM country_subdivisions
    WHERE Level.GetLevel() = (SELECT MAX(Level.GetLevel()) FROM country_subdivisions)
),
CandidateAncestors AS (
    -- 关联每个最低层级节点的所有有边界的祖先（包含自身）
    SELECT 
        b.IsoCode AS BottomNodeCode,
        c.IsoCode,
        c.Level.GetLevel() AS NodeLevel
    FROM BottomLevelNodes b
    INNER JOIN country_subdivisions c 
        ON b.Level.IsDescendantOf(c.Level) = 1 
        AND b.CountryISOAlpha2Code = c.CountryISOAlpha2Code
    WHERE c.HasBoundaries = 1
),
RankedAncestors AS (
    -- 对每个最低层级节点的候选祖先按层级倒序排序，层级越大离当前节点越近
    SELECT 
        IsoCode,
        ROW_NUMBER() OVER (PARTITION BY BottomNodeCode ORDER BY NodeLevel DESC) AS rn
    FROM CandidateAncestors
)
-- 取每个最低层级节点最近的有边界祖先，去重后返回完整字段
SELECT DISTINCT cs.*
FROM RankedAncestors ra
INNER JOIN country_subdivisions cs ON ra.IsoCode = cs.IsoCode
WHERE ra.rn = 1

原有查询的问题说明 #

你之前的查询仅筛选了「自身有边界，且子树内不存在更深的有边界节点」的记录，仅覆盖了省份自身有边界的场景，没有处理底层省份无边界、需要向上取最近有边界祖先的场景，因此会遗漏Lombardia、Lazio、Italy这类需要向上回溯的结果。

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