第 2 课：创建和管理层次结构表中的数据

2025-02-04

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Microsoft Fabric 预览版中的 SQL 数据库

在第 1 课中，你修改了一个现有表以使用 hierarchyid 数据类型，并采用现有数据的表示形式填充 hierarchyid 列。在本课程中，从新表开始，使用分层方法插入数据。然后，使用分层方法查询和操作数据。

Prerequisites

要完成本教程，需要 SQL Server Management Studio、针对运行 SQL Server 的服务器的访问权限以及 AdventureWorks2022 数据库。

此处提供了在 SSMS 中还原数据库的说明：使用 SSMS 还原数据库备份。

使用 hierarchyid 数据类型创建表

以下示例创建了一个名为 EmployeeOrg 的表，其中包括员工数据及其报告层次结构。本例在 AdventureWorks2022 数据库中创建该表，但这是可选操作。为了简化该示例，此表仅包含五列：

OrgNode 是一个存储层次结构关系的 hierarchyid 列。
OrgLevel 是一个计算列，它基于存储层次结构中的各节点级别的 OrgNode 列。它用于广度优先索引。
EmployeeID 包含用于诸如工资单等应用程序的典型员工标识号。在新应用程序的开发过程中，应用程序可以使用 OrgNode 列，不需要这个单独的 EmployeeID 列。
EmpName 包含员工的姓名。
Title 包含员工的职位。

创建 EmployeeOrg 表

在“查询编辑器”窗口中，运行以下代码来创建 EmployeeOrg 表。使用聚集索引将 OrgNode 列指定为主键会创建深度优先索引：

USE AdventureWorks2022;
GO

IF OBJECT_ID('HumanResources.EmployeeOrg') IS NOT NULL
    DROP TABLE HumanResources.EmployeeOrg

CREATE TABLE HumanResources.EmployeeOrg (
    OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,
    OrgLevel AS OrgNode.GetLevel(),
    EmployeeID INT UNIQUE NOT NULL,
    EmpName VARCHAR(20) NOT NULL,
    Title VARCHAR(20) NULL
);
GO

运行下面的代码，对 OrgLevel 和 OrgNode 列创建组合索引，以便支持高效的广度优先搜索：
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

现在即可为该表填充数据。下一个任务将通过使用分层方法来填充该表。

使用分层方法填充层次结构表

AdventureWorks2022 有 8 名在市场营销部门工作的员工。雇员的层次结构如下所示：

David (EmployeeID 6) 是营销经理。 David 下辖三名市场营销专员，他们分别是：

Sariya、EmployeeID 46
John、EmployeeID 271
Jill、EmployeeID 119

营销助理 Wanida (EmployeeID 269) 向 Sariya 报告，营销助理 Mary (EmployeeID 272) 向 John 报告。

插入层次结构树的根

以下示例将市场营销经理 David 插入层次结构根处的表中。 OrdLevel 列是计算列。因此，它不是 INSERT 语句的一部分。此第一条记录使用 GetRoot（数据库引擎）方法将第一条记录填充为层次结构的根。
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

执行以下代码来检查表中的初始行：

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

结果集如下。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager

如上一课所述，使用 ToString() 方法将 hierarchyid 数据类型转换成更易于理解的格式。

插入下属雇员

Sariya 向 David 报告。若要插入 Sariya's 节点，必须创建数据类型为 OrgNode 的适当值。下面的代码创建一个数据类型为 hierarchyid 的变量，并用表的根 OrgNode 值填充此变量。然后使用该变量和 GetRoot（数据库引擎）方法插入从属节点行。 GetDescendant 采用两个参数。检查以下选项的参数值：
- 如果父级为 NULL，则 GetDescendant 返回 NULL。
- 如果父级不是 NULL，并且 child1 和 child2 是 NULL，则 GetDescendant 返回父级的子级。
- 如果父级和 child1 不是 NULL，并且 child2 是 NULL，则 GetDescendant 返回大于 child1 的父级的子级。
- 如果父级和 child2 不是 NULL，并且 child1 是 NULL，则 GetDescendant 返回小于 child2 的父级的子级。
- 如果父级、child1 和 child2 都不为 NULL，则 GetDescendant 返回大于 child1 且小于 child2 的父级的子级。
下面的代码使用根父级的 (NULL, NULL) 参数，因为表中除了根之外还没有任何行。执行以下代码以插入 Sariya：
```
DECLARE @Manager HIERARCHYID
SELECT @Manager = HIERARCHYID::GetRoot()
FROM HumanResources.EmployeeOrg;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist');
```

从第一个过程重复查询来对表进行查询并查看项的显示方式：

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

结果集如下。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist

创建输入新节点的过程

若要简化数据的输入，请创建下面的存储过程以向 EmployeeOrg 表添加员工。该过程接受有关将要添加的雇员的输入值。这包括新员工的经理的 EmployeeID、新员工的 EmployeeID 号以及他们的名字和职位。该过程使用 GetDescendant() 和 GetRoot（数据库引擎）方法。执行下面的代码以创建此过程：

CREATE PROCEDURE AddEmp (
    @mgrid INT,
    @empid INT,
    @e_name VARCHAR(20),
    @title VARCHAR(20)
)
AS
BEGIN
    DECLARE @mOrgNode HIERARCHYID, @lc HIERARCHYID;

    SELECT @mOrgNode = OrgNode
    FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = @mgrid;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @lc = max(OrgNode)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @mOrgNode;

    INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
    VALUES (@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title);

    COMMIT;
END;
GO

以下示例添加了直接或间接向 David 报告的其余四名员工。

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist';
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist';
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant';
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant';

再次执行下面的查询以检查 EmployeeOrg 表中的行：

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

结果集如下。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- --------------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

现在，市场营销组织已完全填充在表中。

使用层次结构方法查询层次结构表

由于 HumanResources.EmployeeOrg 表已完全填充，此任务将说明如何使用某些分层方法来查询层次结构。

查找从属节点

Sariya 有一名下属雇员。若要查询 Sariya 的下属，请执行使用 IsDescendantOf（数据库引擎）方法的以下查询：
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
结果同时列出了 Sariya 和 Wanida。 Sariya 之所以列出，是因为该值是 0 级后代。 Wanida 是 1 级后代。
也可以使用 GetAncestor（数据库引擎）方法查询此信息。 GetAncestor 对尝试返回的级别采用了一个参数。由于 Wanida 位于 Sariya 下面一级，因此使用 GetAncestor(1) ，如以下代码所示：
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee
```
这次，结果仅列出 Wanida。

现在，将 @CurrentEmployee 更改为 David (EmployeeID 6)，并将级别更改为 2。执行以下过程也会返回 Wanida：

DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 6;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee

这次，还收到向 David 报告的位于两个级别之下的 Mary。

使用 GetRoot 和 GetLevel

随着层次结构不断扩大，更加难于确定成员在层次结构中的位置。使用 GetLevel（数据库引擎）方法可查明每一行下方有多少个级别处于层次结构中。执行下面的代码以查看所有行的下属级别：
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
使用 GetRoot（数据库引擎）方法查找层次结构中的根节点。下面的代码返回一个作为根的行：
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

使用分层方法对层次结构表中的数据重新排序

适用范围：SQL Server

重新组织层次结构是一项常见的维护任务。在此任务中，使用包含 UPDATE方法的语句首先将一行移到层次结构的新位置。然后，我们将整个子树移到一个新位置。

GetReparentedValue 方法使用两个参数。第一个参数用于描述要修改的层次结构部分。例如，如果层次结构为 /1/4/2/3/，你希望更改 /1/4/ 部分，将层次结构变为 /2/1/2/3/，后两个节点 (2/3/) 保持不变，则必须提供要更改的节点 (/1/4/) 作为第一个参数。第二个参数提供新的层次结构级别，在示例中为 /2/1/。这两个参数不必包含相同数量的级别。

将一行移到层次结构中的新位置上

当前，Wanida 向 Sariya 报告。在此过程中，将 Wanida 从其当前节点 /1/1/ 移出，使此人向 Jill 报告。新节点将变为 /3/1/，而 /1/ 是第一个参数，/3/ 是第二个参数。这些值与 Sariya 和 Jill 的 OrgNode 值相对应。执行下列代码将 Wanida 从 Sariya 的组织移到 Jill 的组织中：

DECLARE @CurrentEmployee HIERARCHYID,
    @OldParent HIERARCHYID,
    @NewParent HIERARCHYID;

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 269;

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;

UPDATE HumanResources.EmployeeOrg
SET OrgNode = @CurrentEmployee.GetReparentedValue(@OldParent, @NewParent)
WHERE OrgNode = @CurrentEmployee;
GO

执行下面的代码以查看结果：

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

现在，Wanida 位于节点 /3/1/。

重新组织层次结构中的某一部分

为了演示如何同时移动大量人员，请先执行下列代码，添加一个向 Wanida 报告的实习生：
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

现在，Kevin 向 Wanida 报告，Wanida 向 Jill 报告，而 Jill 向 David 报告。也就是说，Kevin 位于级别 /3/1/1/。若要将 Jill 的所有下属都移到一位新经理之下，需要将 /3/ 为 OrgNode 的所有节点更新为新值。执行以下代码更新为 Wanida 向 Sariya 报告，但保持 Kevin 向 Wanida 报告：

DECLARE @OldParent HIERARCHYID,
    @NewParent HIERARCHYID

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;-- Jill

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;-- Sariya

DECLARE children_cursor CURSOR
FOR
SELECT OrgNode
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @OldParent;

DECLARE @ChildId HIERARCHYID;

OPEN children_cursor

FETCH NEXT
FROM children_cursor
INTO @ChildId;

WHILE @@FETCH_STATUS = 0
BEGIN
    START:

    DECLARE @NewId HIERARCHYID;

    SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @NewParent;

    UPDATE HumanResources.EmployeeOrg
    SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
    WHERE OrgNode.IsDescendantOf(@ChildId) = 1;

    IF @@error <> 0
        GOTO START -- On error, retry

    FETCH NEXT
    FROM children_cursor
    INTO @ChildId;
END

CLOSE children_cursor;

DEALLOCATE children_cursor;

执行下面的代码以查看结果：

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

结果集如下。

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/1/1/1/      0x5AD0  3        291        Kevin   Marketing Intern
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

之前向 Jill 报告的整个组织树（Wanida 和 Kevin）现在均向 Sariya 报告。

有关用于重新组织层次结构部分的存储过程，请参阅分层数据（SQL Server）的“移动子树”部分。

反馈

此页面是否有帮助？

通过

第 2 课：创建和管理层次结构表中的数据

Prerequisites

使用 hierarchyid 数据类型创建表

创建 EmployeeOrg 表

使用分层方法填充层次结构表

插入层次结构树的根

插入下属雇员

创建输入新节点的过程

使用层次结构方法查询层次结构表

查找从属节点

使用 GetRoot 和 GetLevel

使用分层方法对层次结构表中的数据重新排序

将一行移到层次结构中的新位置上

重新组织层次结构中的某一部分

反馈

其他资源