使用分层方法对层次结构表中的数据重新排序
重新组织层次结构是一项常见的维护任务。在此任务中,我们将使用包含 GetReparentedValue 方法的 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 的所有下属都移到一位新经理之下,我们需要将 OrgNode 为 /3/ 的所有节点更新为新值。执行下列代码以便将 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//2 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 报告。
有关重新组织层次结构中的某一部分的存储过程的信息,请参阅使用 hierarchyid 数据的“移动子树”部分。