SQL Server 使用 Hierarchyid 操作层次结构数据

lovegigi

　　层次结构数据定义为一组通过层次结构关系互相关联的数据项。 在层次结构关系中，一个数据项是另一个项的父级或子级。
　　sql server2008开始内置的 hierarchyid 数据类型使存储和查询层次结构数据变得更为容易。hierarchyid 其实是 CLR 数据类型。
　　废话不多说，看不明白就实际操作一把，然后再回过头来理解。
　　下面先创建一个表，并插入一些数据：
　　

create table RoleMan　　
(
　　NodeId    hierarchyid
not null,　　RoleId   
int not null,　　RoleName   
nvarchar(32) not null,　　Par_NodeId   
as NodeId.GetLevel()　　
)
　　
go
　　

　　
insert into RoleMan(NodeId,RoleId,RoleName)
　　
select '/1/','1','超级管理员' union
　　
select '/1/1/','2','管理员A' union
　　
select '/1/2/','3','管理员B' union
　　
select '/1/1/1/','4','用户AA' union
　　
select '/1/1/2/','5','用户AB' union
　　
select '/1/2/1/','6','用户BA' union
　　
select '/1/2/2/','7','用户BB'
　　
go
　　

　　
select *,
　　
NodeId.ToString() NodeId_Path   
　　
from RoleMan
　　

　　查询指定节点的祖先节点：
　　

　　
declare @NodeId    hierarchyid
　　

　　
select @NodeId=NodeId
　　
from RoleMan
　　
where RoleId=5
　　

　　
select *,NodeId.ToString() NodeId_Path
　　
from RoleMan
　　
where @NodeId.IsDescendantOf(NodeId)=1   
　　

　　查询指定节点的子孙节点：
　　

　　
declare @NodeId    hierarchyid
　　

　　
select @NodeId=NodeId
　　
from RoleMan
　　
where RoleId=2
　　

　　
select *,NodeId.ToString() NodeId_Path
　　
from RoleMan
　　
where NodeId.IsDescendantOf(@NodeId)=1   
　　

　　返回指定层级的所有节点：
　　

　　
declare @NodeId    hierarchyid
　　

　　
select @NodeId=NodeId
　　
from RoleMan
　　
where Par_NodeId=1        
　　

　　
select @NodeId
　　
select *,NodeId.ToString() NodeId_Path
　　
from RoleMan
　　
where NodeId.GetAncestor(0)=@NodeId   
　　

　　
select @NodeId
　　
select *,NodeId.ToString() NodeId_Path
　　
from RoleMan
　　
where NodeId.GetAncestor(1)=@NodeId   
　　

　　插入新节点：
　　

declare @PNodeId hierarchyid　　

declare @NodeId    hierarchyid　　

　　

select @PNodeId=NodeId　　

from RoleMan　　

where RoleId=3　　

　　
select @NodeId=NodeId
　　
from RoleMan
　　
where RoleId=7        
　　

　　
insert into RoleMan(NodeId,RoleId,RoleName)　　
　　
values(@PNodeId.GetDescendant(@NodeId, NULL),'8','用户BC')　　--即在父节点为 '管理员B' 下面的子节点 '用户BB' 后面添加一个节点 '用户BC'
　　

　　
select *,
　　
NodeId.ToString() NodeId_Path   
　　
from RoleMan
　　

　　当然，这是父节点下面存在着子节点的时候，那么当父节点下面没有子节点应该怎么添加呢？只需要将 GetDescendant(null,null) 的两个参数都设置为null就行了。如下：
　　

declare @PNodeId hierarchyid　　

　　

select @PNodeId=NodeId　　

from RoleMan　　

where RoleId=8　　

　　
insert into RoleMan(NodeId,RoleId,RoleName)
　　
values(@PNodeId.GetDescendant(null, NULL),'9','用户BCA')   
　　

　　
select *,
　　
NodeId.ToString() NodeId_Path   
　　
from RoleMan
　　

　　如果需要在一个父节点下面的两个子节点之间插入一个子节点，就需要将 GetDescendant(@Child1,@Child2) 的两个参数同时指定。如下：
　　

declare @PNodeId hierarchyid　　

declare @Child1    hierarchyid　　

declare @Child2 hierarchyid　　

　　

select @PNodeId=NodeId　　

from RoleMan　　

where RoleId=2　　

　　
select @Child1=NodeId  
　　
from RoleMan
　　
where RoleId=4        
　　

　　
select @Child2=NodeId  
　　
from RoleMan
　　
where RoleId=5        
　　

　　
insert into RoleMan(NodeId,RoleId,RoleName)
　　
values(@PNodeId.GetDescendant(@Child1, @Child2),'10','用户A插队')
　　

　　
select *,
　　
NodeId.ToString() NodeId_Path   
　　
from RoleMan
　　

　　变更节点位置：
　　变更节点位置应当使用 GetReparentedValue 方法，该方法接受两个参数，一个是原节点的 hierarchyid，另一个是目标节点 hierarchyid。
　　

　　
declare @RawNodePath hierarchyid
　　
declare @NewNodePath hierarchyid
　　

　　
select @RawNodePath=NodeId  
　　
from RoleMan
　　
where RoleId=6        
　　

　　
select @NewNodePath=NodeId
　　
from RoleMan
　　
where RoleId=2        
　　

　　
select @NewNodePath=@NewNodePath.GetDescendant(MAX(NodeId), NULL)   
　　
from RoleMan
　　
where NodeId.GetAncestor(1)=@NewNodePath   
　　

　　
update RoleMan
　　
set NodeId=NodeId.GetReparentedValue(@RawNodePath, @NewNodePath)
　　
where NodeId.IsDescendantOf(@RawNodePath) = 1
　　

　　
select *,
　　
NodeId.ToString() NodeId_Path   
　　
from RoleMan
　　

　　
go
　　

　　hierarchyid 函数：
　　GetLevel()：用于确定当前层次的深度（级别），最顶层（根节点）为0，然后依次加1。
　　ToString()：因为 hierarchyid 类型的值是以16进制表示的，ToString()用于将 hierarchyid 类型转换为字符串类型。
　　IsDescendantOf()：判断指定节点是否是另一个节点的后代，如果是，则返回1。一个参数，为指定的节点。
　　GetAncestor(n)：n=0时，会返回当前层级当前节点的数据。否则，会返回指定层级的下 n 级的所有节点。
　　GetDescendant(null,null) ：用于添加节点，该方法接受两个参数，可为空，第一个子节点，第二个子节点。如果第一个参数不为空，表示在指定的父节点下面的子节点后面添加节点，如果两个参数皆为空，则表示要在没有子节点的节点添加节点。
　　GetReparentedValue()：用于变更节点位置，该方法接受两个参数，一个是原节点的hierarchyid，另一个是目标节点hierarchyid。
　　GetRoot()：获取节点的根。
　　Parse()：将字符串转换为 hierarchyid 。该字符串的格式通常都是/1/这样的。
　　Read()：Read 从传入的 BinaryReader 读取 SqlHierarchyId 的二进制表示形式，并将 SqlHierarchyId 对象设置为该值。不能使用 Transact-SQL 调用 Read。请改为使用 CAST 或 CONVERT。
　　Write()：Write 将 SqlHierarchyId 的二进制表示形式写出到传入的 BinaryWriter 中。无法通过使用 Transact-SQL 来调用 Write。请改为使用 CAST 或 CONVERT。
　　hierarchyid 索引策略：
　　用于对层次结构数据进行索引的策略有两种：深度优先和广度优先。
　　深度优先索引，子树中各行的存储位置相邻，简而言之，就是以 hierarchyid 值排序的方式存储。
　　创建深度优先索引：
　　

　　
create unique index Role_Depth_First
　　
on RoleMan(NodeId)
　　
go
　　

　　
select *,NodeId.ToString() NodeId_Path
　　
from RoleMan
　　
order by NodeId
　　

　　广度优先索引，是将层次结构中每个级别的各行存储在一起，简而言之，就是按层级排序的方式存储。
　　创建广度优先索引：
　　

　　
create clustered index Role_Breadth_First   
　　
on RoleMan(Par_NodeId,NodeId) ;  
　　
go  
　　

　　
select *,NodeId.ToString() NodeId_Path
　　
from RoleMan
　　
order by Par_NodeId,NodeId
　　

　　参考：
　　http://blog.csdn.net/zhanghongju/article/details/42584643
　　https://msdn.microsoft.com/zh-cn/library/bb677173.aspx