主页 / 问题 / 923752
Accepted
Иван К
Asked:2020-12-21 18:38:55 +0000 UTC2020-12-21 18:38:55 +0000 UTC

获取 MS SQL 分层表中元素的完整路径（使用 hierarchyid）

  • 772

有这样一个分层数据结构：

1 Проект
  2 Этап
    3 Документация
      4 Чертёж

这里数字表示（相对而言）hierarchyid字段，name表示表中的name字段。MS SQL 定义了 ToString() 方法，例如，它允许您获取最后一个元素（以下内容：

4.ToString()

/1/2/3/4/

但是这个选项不适合我，我想得到以下结果：

/Проект/Этап/Документация/Чертёж/

你能告诉我如何获得这样的完整路径吗？

база-данных

1 个回答

  1. Best Answer

    让有数据

    DECLARE @data TABLE (path hierarchyid, name nvarchar(50));

INSERT INTO @data
VALUES
    ('/1/', N'AA'),
    ('/1/1/', N'BBB'),
    ('/1/1/1/', N'CCCC'),
    ('/1/1/1/1/', N'DDDDD');

    和一个包含感兴趣节点路径的变量

    DECLARE @node hierarchyid = '/1/1/1/1/';

    然后在 SqlServer 2017（及更高版本）中，您可以像这样连接节点名称：

    SELECT
    '/' + STRING_AGG(d.name, '/') WITHIN GROUP (ORDER BY l.level DESC) + '/'
FROM (VALUES (0), (1), (2), (3)) l(level)
    JOIN @data d ON d.path = @node.GetAncestor(level)

    对于早期版本，如下所示：

    SELECT
    '/' + (
        SELECT
            d.name + '/'
        FROM (VALUES (0), (1), (2), (3)) l(level)
            JOIN @data d ON d.path = @node.GetAncestor(level)
        ORDER BY l.level DESC
        FOR XML PATH(''), TYPE
    ).value('text()[1]', 'nvarchar(max)')

    如果有超过四个级别，则(VALUES (0), (1), (2), (3)) l(level)必须添加常量。

    如果事先不知道级别数，则可以使用数字生成函数代替一组数字，例如Itzik Ben-Gan 的 GetNumsRange（此处）。您还需要一个用于节点深度的附加变量

    DECLARE @level int = @node.GetLevel();

    分别在请求中而不是

    (VALUES (0), (1), (2), (3)) l(level)

    将需要被替换

    (SELECT n FROM dbo.GetNumsRange(0, @level - 1)) l(level)
    • 1

相关问题