锋利的SQL2014：层次结构操作之Hierarchyid(二)

2

/1/1/

0x5AD6

6

员工A

3

/1/1/1/

在实际应用环境中，处于性能考虑，通常是仅读取必要层次的数据，而不是像上面这样全部读取。例如，为了填充一个组织的树形结构，我们可能仅填充树形的主干，当用户单击主干的某个节点时再读取其中的数据。hierarchyid的GetAncestor方法就提供了这种分层检索功能。例如，下面的语句用于返回根节点的第一层数据，即副总经理级，查询结果如表19-30所示。

DECLARE @EmpNode AShierarchyid;

SELECT @EmpNode = CAST('/'AS hierarchyid);

SELECT *,Org_Node.ToString() AS Org_Node_Str

FROM dbo.Employees

WHEREOrg_Node.GetAncestor(1) = @EmpNode;

表19-30 根节点的第一层数据

使用GetAncestor(0)会返回当前层级的数据。对于上面的语句而言，返回的是根节点的数据。

19.8.3 插入新节点

可以像前面创建表时那样，直接指定节点层次来插入节点，例如，下面的语句在部门经理B下新增了一名雇员。

INSERT INTO dbo.EmployeesVALUES

('/1/2/1/',7,'员工B');

但是在层次比较多时，这种方式很有可能因为疏忽而造成节点隶属关系错误。比较有效的方法是使用GetDescendant方法。例如，下面的示例中第一条SELECT语句用于获得部门经理B的节点（即“/1/2/”），第二条SELECT语句用于获得部门经理B下面员工B的节点（即“/1/2/1/”），INSERT语句的作用是在部门经理B下面、员工B的后面新增一个节点（即“/1/2/2/”）。

DECLARE @Manager AShierarchyid, @Child1 AS hierarchyid;

SELECT @Manager = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 5; --获取部门经理B的节点

SELECT @Child1 = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 7; --获取上面语句新增员工B的节点

INSERT INTO dbo.EmployeesVALUES --在“/1/2/1/”后面新增一个节点

(@Manager.GetDescendant(@Child1, NULL), 8, '员工C');

执行下面的语句，查询结果如表19-31所示。

SELECT *, Org_Node.ToString()AS Org_Node_Str

FROM dbo.Employees;

表19-31 使用GetDescendant插入节点后的数据

对于父节点具有子节点的情况，应当像上面这样为GetDescendant指定Child1参数。如果没有，则可以使用NULL代替。例如，由表19-31可以看出，副总经理B下面没有节点，如果需要在其下面插入一个子节点，就可以使用NULL。参考下面的语句：

DECLARE @Manager AShierarchyid;

SELECT @Manager = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 3; --获取副总经理B的节点

INSERT INTO dbo.EmployeesVALUES --在“/2/”下面新增一个节点

(@Manager.GetDescendant(NULL, NULL), 9, '部门经理C');

部门经理C的节点为“/2/1/”。

如果需要在员工B和员工C之间插入一个节点，应当同时指定Child1和Child2参数。参考下面的语句：

DECLARE @Manager AShierarchyid,

@Child1 AS hierarchyid,

@Child2 AS hierarchyid;

SELECT @Manager = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 5; --获取部门经理B的节点

SELECT @Child1 = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 7; --获取员工B的节点

SELECT @Child2 = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 8; --获取员工C的节点

INSERT INTO dbo.EmployeesVALUES

(@Manager.GetDescendant(@Child1, @Child2),10, '员工D');

执行下面的语句，查询结果如表19-32所示。可以看到，节点使用了小数格式，即“/1/2/1.1/”。

SELECT *,Org_Node.ToString() AS Org_Node_Str

FROM dbo.Employees;

表19-32 在员工B和员工C之间插入节点

19.8.4 变更节点位置

变更节点位置应当使用GetReparentedValue方法，该方法接受两个参数，一个是原节点的hierarchyid，另一个是目标节点hierarchyid。由表19-32可以看出，部门经理A隶属于副总经理A，下面的语句将其调整到副总经理B下面。

DECLARE @oldRoot AShierarchyid,

@newRoot AS hierarchyid;

SELECT @oldRoot = Org_Node

FROM dbo.Employees

WHERE EmployeeId = 4; --当前节点为“/1/1/”

SELECT @newRoot =CAST('/2/1/' AS hierarchyi