如何将竖线分隔的位置字符串转换为MS SQL层级表并实现层级查询？

原始数据 #

现有一张存储地理位置字符串的表，每条记录以|分隔层级，条目层级不固定且存在重复，共约10000条记录，唯一位置信息约200个。示例数据：

"Germany|Berlin|Hauptstrasse|123"
"France|Paris|CDG-Chausee"
"Germany|Munich|Bavariaplatz|234"
"Germany|Munich|Ludwigsallee"
"France|Paris|CDG-Chausee"
...(约10000条更多记录)

查询需求 #

需转换为层级表（非平衡树），支持两类查询：

• a) 查询指定节点的直接子节点：

  示例："Munich" → Bavariaplatz, Ludwigsallee
  示例："Germany" → Berlin, Munich
  

• b) 查询指定节点下所有子节点的数量及列表：

  示例："Munich" → 3 (Bavariaplatz, Ludwigsallee, 234)
  示例："Germany" → 7 (Berlin, Munich, Hauptstrasse, Bavariaplatz, Ludwigsallee, 123, 234)
  

目标表结构 #

预期表结构如下（含自增ID、节点名、层级、父节点ID、全路径）：

id; name;    level; parentId; sortPath
 1; France;       1; NULL; "France"
 2; Paris;        2;    1; "France|Paris"
 3; CDG-Chausee;  3;    2; "France|Paris|CDG-Chausee"
 4; Germany;      1; NULL; "Germany" 
 5; Berlin;       2;    4; "Germany|Berlin"
 6; Hauptstrasse; 3;    5; "Germany|Berlin|Hauptstrasse"
 7; 123;          4;    6; "Germany|Berlin|Hauptstrasse|123"
 8; Munich;       2;    4; "Germany|Munich"
 9; Bavariaplatz; 3;    8; "Germany|Munich|Bavariaplatz" 
10; 234;          4;    9; "Germany|Munich|Bavariaplatz|234"
11; Ludwigsallee; 3;    8; "Germany|Munich|Ludwigsallee"

遇到的问题 #

尝试过递归CTE和Transact SQL但未成功，思路为：先排序字符串、逐层提取节点去重并关联父节点；同时想了解是否可用MS SQL的HierarchyId类型简化实现（比如将|替换为点）。

以下提供两种实现方案，分别适配无HierarchyId经验和希望简化操作的场景：

方案一：无HierarchyId的递归CTE实现 #

步骤1：拆分原始数据为层级节点 #

先将原始字符串拆分为单个节点，记录每个节点的全路径和层级：

-- 假设原始表名为LocationRaw，字段名为LocationString
DROP TABLE IF EXISTS #SplitNodes;
CREATE TABLE #SplitNodes (
    FullPath VARCHAR(MAX),
    NodeName VARCHAR(255),
    Level INT
);

WITH SplitCTE AS (
    SELECT 
        TRIM('"' FROM LocationString) AS FullPath,
        CAST('<root><n>' + REPLACE(TRIM('"' FROM LocationString), '|', '</n><n>') + '</n></root>' AS XML) AS XmlPath
    FROM LocationRaw
    GROUP BY TRIM('"' FROM LocationString) -- 先去重原始路径，减少计算量
)
INSERT INTO #SplitNodes (FullPath, NodeName, Level)
SELECT DISTINCT
    sc.FullPath,
    x.n.value('.', 'VARCHAR(255)'),
    pos.value('position()[1]', 'INT') AS Level
FROM SplitCTE sc
CROSS APPLY sc.XmlPath.nodes('/root/n') x(n)
CROSS APPLY x.n.nodes('position()') pos;

步骤2：生成目标层级表（去重+关联父节点） #

DROP TABLE IF EXISTS LocationHierarchy;
CREATE TABLE LocationHierarchy (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    level INT NOT NULL,
    parentId INT NULL,
    sortPath VARCHAR(MAX) NOT NULL,
    UNIQUE(sortPath) -- 确保路径唯一，避免重复节点
);

-- 插入所有唯一的节点路径
WITH UniquePaths AS (
    SELECT DISTINCT
        STUFF((
            SELECT '|' + s2.NodeName
            FROM #SplitNodes s2
            WHERE s2.FullPath = s.FullPath AND s2.Level <= s.Level
            ORDER BY s2.Level
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS sortPath,
        s.NodeName,
        s.Level
    FROM #SplitNodes s
)
INSERT INTO LocationHierarchy (name, level, sortPath)
SELECT NodeName, Level, sortPath
FROM UniquePaths;

-- 更新父节点ID：通过父路径匹配
UPDATE child
SET parentId = parent.id
FROM LocationHierarchy child
JOIN LocationHierarchy parent ON 
    child.level = parent.level + 1 
    AND LEFT(child.sortPath, LEN(child.sortPath) - LEN(child.name) - 1) = parent.sortPath;

方案二：用HierarchyId简化实现 #

HierarchyId是MS SQL专为层级数据设计的类型，可自动处理层级关系，无需手动维护parentId和sortPath：

步骤1：创建带HierarchyId的层级表 #

DROP TABLE IF EXISTS LocationHierarchy_HID;
CREATE TABLE LocationHierarchy_HID (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    hierarchy hierarchyid NOT NULL,
    level AS hierarchy.GetLevel() + 1 PERSISTED, -- 层级从1开始（匹配目标表结构）
    sortPath AS REPLACE(SUBSTRING(hierarchy.ToString(), 2, LEN(hierarchy.ToString())-2), '/', '|') PERSISTED, -- 生成|分隔的全路径
    UNIQUE(hierarchy)
);

步骤2：拆分原始数据并插入层级表（自动去重） #

WITH SplitPaths AS (
    SELECT DISTINCT TRIM('"' FROM LocationString) AS RawPath
    FROM LocationRaw
),
HierarchyNodes AS (
    SELECT
        RawPath,
        CAST('<root><n>' + REPLACE(RawPath, '|', '</n><n>') + '</n></root>' AS XML) AS XmlPath
    FROM SplitPaths
),
AllNodes AS (
    SELECT DISTINCT
        x.n.value('.', 'VARCHAR(255)') AS NodeName,
        CAST(
            '/' + STUFF((
                SELECT '/' + s2.n.value('.', 'VARCHAR(255)')
                FROM HierarchyNodes h2
                CROSS APPLY h2.XmlPath.nodes('/root/n[position() <= sql:column("pos")]') s2(n)
                WHERE h2.RawPath = h.RawPath
                ORDER BY position()
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') + '/' AS hierarchyid
        ) AS NodeHierarchy
    FROM HierarchyNodes h
    CROSS APPLY h.XmlPath.nodes('/root/n') x(n)
    CROSS APPLY x.n.nodes('position()') pos
)
INSERT INTO LocationHierarchy_HID (name, hierarchy)
SELECT NodeName, NodeHierarchy
FROM AllNodes;

需求a：查询指定节点的直接子节点 #

方案一（无HierarchyId） #

SELECT name
FROM LocationHierarchy
WHERE parentId = (SELECT id FROM LocationHierarchy WHERE name = 'Munich');

方案二（HierarchyId） #

SELECT name
FROM LocationHierarchy_HID
WHERE hierarchy.IsDescendantOf((SELECT hierarchy FROM LocationHierarchy_HID WHERE name = 'Munich')) = 1
AND level = (SELECT level + 1 FROM LocationHierarchy_HID WHERE name = 'Munich');

需求b：查询指定节点下所有子节点的数量及列表 #

方案一（无HierarchyId） #

-- 子节点数量
SELECT COUNT(*) AS ChildCount
FROM LocationHierarchy
WHERE sortPath LIKE (SELECT sortPath + '|%' FROM LocationHierarchy WHERE name = 'Germany');

-- 子节点列表
SELECT name
FROM LocationHierarchy
WHERE sortPath LIKE (SELECT sortPath + '|%' FROM LocationHierarchy WHERE name = 'Germany');

方案二（HierarchyId） #

-- 子节点数量
SELECT COUNT(*) AS ChildCount
FROM LocationHierarchy_HID
WHERE hierarchy.IsDescendantOf((SELECT hierarchy FROM LocationHierarchy_HID WHERE name = 'Germany')) = 1
AND hierarchy <> (SELECT hierarchy FROM LocationHierarchy_HID WHERE name = 'Germany');

-- 子节点列表
SELECT name
FROM LocationHierarchy_HID
WHERE hierarchy.IsDescendantOf((SELECT hierarchy FROM LocationHierarchy_HID WHERE name = 'Germany')) = 1
AND hierarchy <> (SELECT hierarchy FROM LocationHierarchy_HID WHERE name = 'Germany');

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