SQL Server 使用 Hierarchyid 操作层次组织数据

层次结构数据定义也同样组通过层次结构关系相互关联的数据项。
在层次结构关系受到,一个数据项是其它一个宗之父级或子级。

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()    -- GetLevel()用于确定当前层次的深度(级别),最顶层(根节点)为0,然后依次加1。
)
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    -- 因为 hierarchyid 类型的值是以16进制表示的,这里把他转换为字符串
from RoleMan

图片 1

查询指定节点的先人节点:

-- 查询指定节点的祖先节点
declare @NodeId    hierarchyid

select @NodeId=NodeId 
from RoleMan 
where RoleId=5

select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where @NodeId.IsDescendantOf(NodeId)=1    -- IsDescendantOf(NodeId),判断指定节点是否是另一个节点的后代,如果是,则返回1

图片 2

询问指定节点的子孙节点:

-- 查询指定节点的子孙节点
declare @NodeId    hierarchyid

select @NodeId=NodeId
from RoleMan 
where RoleId=2

select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where NodeId.IsDescendantOf(@NodeId)=1    -- IsDescendantOf(NodeId),判断指定节点是否是另一个节点的后代,如果是,则返回1

图片 3

回到指定层级的具备节点:

-- 返回指定层级的所有节点
declare @NodeId    hierarchyid

select @NodeId=NodeId
from RoleMan 
where Par_NodeId=1        -- 指定层级为 1 级

select @NodeId
select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where NodeId.GetAncestor(0)=@NodeId    -- GetAncestor(0),会返回当前层级当前节点的数据

select @NodeId
select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where NodeId.GetAncestor(1)=@NodeId    -- GetAncestor(1),会返回指定层级(@NodeId指定为1级)的下一级的所有节点的数据
                                    -- 数值 1 表示要在层次结构中下降的级别数。

图片 4

插新节点:

declare @PNodeId hierarchyid
declare @NodeId    hierarchyid

select @PNodeId=NodeId 
from RoleMan 
where RoleId=3        -- 获取 管理员B 的节点,即用于为添加的节点指定父级

select @NodeId=NodeId 
from RoleMan 
where RoleId=7        -- 获取 用户BB 的节点,即指定添加的节点位于哪个子节点后面

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

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

图片 5

自,这是父节点下面是着子节点的时段,那么当父节点下面没有子节点应该怎么长呢?只待拿
GetDescendant(null,null) 的个别个参数都装也null就执行了。如下:

declare @PNodeId hierarchyid

select @PNodeId=NodeId 
from RoleMan 
where RoleId=8        -- 获取 用户BC 的节点,即用于为添加的节点指定父级

insert into RoleMan(NodeId,RoleId,RoleName)
values(@PNodeId.GetDescendant(null, NULL),'9','用户BCA')    -- 为无子节点的父节点添加子节点

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

图片 6

假设要在一个父节点下面的有限个子节点之间插入一个子节点,就得用
GetDescendant(@Child1,@Child2) 的蝇头独参数同时指定。如下:

declare @PNodeId hierarchyid
declare @Child1    hierarchyid
declare @Child2 hierarchyid

select @PNodeId=NodeId 
from RoleMan 
where RoleId=2        -- 获取 管理员A 的节点,即用于为添加的节点指定父级

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插队')-- 在父节点 管理员A 的子节点 用户AA 和 用户AB 之间插入一个节点 用户A插队

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

图片 7

更改节点位置:

变更节点位置应该采取 GetReparentedValue
方法,该方式接受两个参数,一个凡原来节点的 hierarchyid,另一个是目标节点
hierarchyid。

-- 把 管理员B 节点下面的子节点 用户BA 移动到 管理员A 节点的子节点 用户AB 后面
declare @RawNodePath hierarchyid
declare @NewNodePath hierarchyid

select @RawNodePath=NodeId  
from RoleMan 
where RoleId=6        -- 获取节点 用户BA 

select @NewNodePath=NodeId 
from RoleMan 
where RoleId=2        -- 获取节点 管理员A

select @NewNodePath=@NewNodePath.GetDescendant(MAX(NodeId), NULL)    -- 获取节点 管理员A 下面的最大的子节点,即最后一个子节点
from RoleMan 
where NodeId.GetAncestor(1)=@NewNodePath    -- 获取父节点 管理员A 下面的所有子级

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

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

go

图片 8

 

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

图片 9

广度优先索引,是用层次结构中每个级别之各行存储在联合,简而言之,就是按照层级排序的方囤。

创造广度优先找引:

--创建广度优先索引
create clustered index Role_Breadth_First   
on RoleMan(Par_NodeId,NodeId) ;  
go  

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

图片 10

 

参考:

http://blog.csdn.net/zhanghongju/article/details/42584643

https://msdn.microsoft.com/zh-cn/library/bb677173.aspx

 

相关文章