MSSQL 2017下编写支持按ID查询的树形结构层级编号SQL语句

实现思路 #

使用MSSQL支持的递归公共表表达式（CTE）实现，分为两类使用场景：

• 全表所有节点层级编号查询：从根节点向下递归拼接层级编号
• 指定ID单节点层级编号查询：从目标节点向上递归拼接，查询效率更高

全表查询代码 #

WITH RecursiveHierarchy AS
(
    -- 锚点成员：取所有根节点（无父节点）
    SELECT
        ID,
        Name,
        CAST(OrderNumber AS VARCHAR(200)) AS HierarchicalNumber
    FROM dbo.Correspondents
    WHERE ParentID IS NULL

    UNION ALL

    -- 递归成员：子节点拼接父节点的层级编号
    SELECT
        c.ID,
        c.Name,
        CAST(CONCAT(rh.HierarchicalNumber, '.', c.OrderNumber) AS VARCHAR(200)) AS HierarchicalNumber
    FROM dbo.Correspondents c
    INNER JOIN RecursiveHierarchy rh ON c.ParentID = rh.ID
)
SELECT ID, Name, HierarchicalNumber
FROM RecursiveHierarchy
ORDER BY ID;

执行后输出结果和需求一致：

ID|Name|HierarchicalNumber
1 |   A|   1
2 |   B|   1.2
3 |   C|   1.3
4 |   D|   1.2.1

指定ID查询代码 #

如果只需要查询单个指定ID对应的层级编号，使用以下语句，不需要遍历全表性能更好：

-- 替换为你要查询的目标ID
DECLARE @TargetID SMALLINT = 4;

WITH RecursiveUp AS
(
    -- 锚点成员：取目标节点
    SELECT
        ID,
        ParentID,
        CAST(OrderNumber AS VARCHAR(200)) AS HierarchicalNumber
    FROM dbo.Correspondents
    WHERE ID = @TargetID

    UNION ALL

    -- 递归成员：向上找父节点，拼接在层级编号开头
    SELECT
        c.ID,
        c.ParentID,
        CAST(CONCAT(c.OrderNumber, '.', ru.HierarchicalNumber) AS VARCHAR(200)) AS HierarchicalNumber
    FROM RecursiveUp ru
    INNER JOIN dbo.Correspondents c ON ru.ParentID = c.ID
)
-- 取最长的拼接结果即为完整层级编号
SELECT TOP 1 HierarchicalNumber FROM RecursiveUp ORDER BY LEN(HierarchicalNumber) DESC;

查询ID=4时返回结果为1.2.1，符合预期。

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