澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:Hierarchyid数据类型,数据类

2019-09-11 作者:数据库网络   |   浏览(138)

树形等级次序结构(Hierarchy)平日出现在有协会的数目中,T-SQL新添数据类型HierarchyID, 其尺寸可变,用于存储档次结构中的路线。HierarchyID表示的档次结构是树形的,由应用程序来变化和分红HierarchyID的值,创建父子节点之间的涉及。

往常我们在关周到据库中创建树状结构的时候,平日选用ID ParentID来落到实处两条纪录间的父亲和儿子关系。但这种格局只好标示其相对地方。化解这类难点在SqlServer二零零六出现在此之前常常是使用游标来操作,但熟习数据库内部机制的人都了然使用游标带来的属性难题和其它标题是比较严重的。

档次结构数据定义为一组通过档案的次序结构关系互动关联的数目项。 在档案的次序结构关系中,三个数码项是另一个项的父级或子级。

HierarchyID数据类型补助深度优先顺序的可比,对于多个HierarchyID值 a和b,a<b意味着,在深度优先遍历时,先遍历到a,后遍历到b,也正是说,值越小,越左近根节点。对Hierarchy数据类型创制索引,是遵照深度优先,先左后右的种种来排序的。左和右是基于节点的值来决断的,在同等深度上,值极小的节点在父节点的左臂。

到了SqlServer二零零六下,能够挑选取CTE来做递归查询,这种措施查询相比较简略,但鉴于数据库内部是行使递归查询的艺术,其功效还是不高;为了能够落实既简单又急速的查询,平日的做法是充实冗余字段,比如扩张八个"Path"字段,查询时用模糊查询来打开左相称。对Path建索引后,这种查询的效用还是相当高的,由此这种方法也是一种健康的宏图方法;

sql server2009早先内置的 hierarchyid 数据类型使积攒和查询档期的顺序结构数据变得尤为轻松。hierarchyid 其实是 CLXC90 数据类型。

一,类型的赋值

SQL SEOdysseyVEEnclave2010引进了新的hierarchyid数据类型,能够用它来做地点存款和储蓄况兼在树档次结构中管理其地方.只用那么些函数能精简地代表档案的次序结构中的地点.该函数提供的有个别置于的函数方法能够操作和遍历档次结构,使得存款和储蓄和询问分层数据更是轻便,而没有需求像那样通过CTE递归来获得.

 

HierarchyID数据类型存款和储蓄的是单个节点在树形结构中的路线(Path),路线从根节点(Root Node)初阶,根节点是“/”,路线以“/”结尾,使用整数表示三个节点。那表示HierarchyID的值必得以“/”最初,以“/”结尾,“/”之直接纳数值(正整数或正小数)标志二个因素,举例:“/”,“/56%/”,“/54%/3/”,"/53%.53%"。

Hierarchyid类型其实是二个CLENVISION自定义数据类型依次张开:数据库->系统数据库->master->可编程性->类型->系统数据类型->CL传祺数据类型->hierarchyid,能够看看该数据类型.

废话相当少说,看不知底就实操一把,然后再回过头来通晓。

有3种赋值方式,通过字符串赋值,字符串转变和由此整数赋值。

于hierarchyid有关的一对函数首要有:

上面先成立二个表,并插入一些数据:

declare @ha HierarchyID
declare @hb HierarchyID
declare @hc HierarchyID

set @ha='/1/2/3/'
set @hb=HierarchyID::Parse('/1/2/3/')
set @hc=0x5B5E

select @ha as ha,@hb.ToString() as hb,@hc.ToString() as hc
  • GetAncestor :取得某一个等级的古人
  • GetDescendant :取得某一个等第的后人
  • GetLevel :猎取等第
  • GetRoot :取得根
  • IsDescendantOf :剖断某些节点是还是不是为有个别节点的儿孙
  • Parse :将字符串转换为hierarchyid。该字符串的格式日常都以/1/这样的
  • Read :Read 从传出的BinaryReader 读取SqlHierarchyId 的二进制表示方式,并将SqlHierarchyId 对象设置为该值。无法选拔Transact-SQL 调用Read。请改为利用CAST 或CONVERT。
  • GetReparentedValue :能够用来运动节点(或然子树)
  • ToString :将hierarchyid转换为字符串,与parse正好相反
  • Write : 将SqlHierarchyId 的二进制表示格局写出到传入的BinaryWriter 中。不能够通过应用Transact-SQL 来调用Write。请改为利用CAST 或CONVERT。
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

hierarchyid 数据类型的值表示树档次结构中的地点。hierarchyid 的值具备以下属性:

澳门新萄京官方网站 2

二,按深度优先顺序实行比较 

  • 可怜紧凑

    在具有 n 个节点的树中,表示三个节点所需的平分位数取决于平均端数(节点的平均子级数)。端数比较小时(0-7),大小约为 6*logAn 位,个中 A 是平均端数。对于平均端数为 6 级、满含 100,000 个人的团伙档次结构,贰个节点大概占 三贰十二人。存款和储蓄时,此值向上舍入为 40 位,即 5 字节。

  • 按深度优先顺序进行相比较

    给定八个 hierarchyid 值 aba<b 表示在对树进行深度优先遍历时,先找到 a,后找到 b。hierarchyid 数据类型的目录按深度优先顺序排序,在深度优先遍历中相邻的节点的仓库储存地点也紧邻。譬喻,一条记下的子级的存款和储蓄地方与该记录的蕴藏地方是附近的。

  • 支撑任性插入和删除

    经过行使 GetDescendant 方法,始终能够在随便给定节点的左侧、左侧或随意多个同级节点之间变化同级节点。在档期的顺序结构中插入或删除放肆数目标节点时,该相比较属性保持不改变。大好些个插入和删除操作都封存了紧密性属性。但是,对于在多个节点之间进行的插入操作,所发生的 hierarchyid 值的表示情势在紧密性方面将略微收缩。

询问钦点节点的祖先节点:

给定三个 hierarchyid 值 a 和 b,a<b 表示在对树进行深度优先遍历时,先找到 a,后找到 b。hierarchyid  数据类型的目录按深度优先顺序排序,在深度优先遍历中相邻的节点的贮存地点也紧邻。同级其他节点,左侧节点小于左边节点,表示侧面先被遍历到。

hierarchyid 数据类型具备以下局限性:

-- 查询指定节点的祖先节点
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
declare @ha HierarchyID
declare @hb HierarchyID
declare @hc HierarchyID

set @ha=HierarchyID::Parse('/1/2/')
set @hb=HierarchyID::Parse('/1/2/3/')
set @hc=HierarchyID::Parse('/1/2/4/')

select iif(@ha>=@hb,'>=','<'),iif(@hb>=@hc,'>=','<')
  • 类型为 hierarchyid 的列不会自行表示树。由应用程序来扭转和分配 hierarchyid 值,使行与行之间的所需关系呈现在这么些值中。一些应用程序乃至恐怕无需用项目为 hierarchyid 的列来表示树。或者那个值为对别的表中定义的档案的次序结构中地方的引用。

  • 由应用程序来保管转变和分红 hierarchyid 值时的产出处境。不可能确认保障列中的 hierarchyid 值是独一的,除非应用程序使用独一键约束或应用程序自己通过投机的逻辑来强制达成独一性。

  • 由 hierarchyid 值表示的档案的次序结构关系不是像外键关系那样强制实现的。或许会产出上面这种档期的顺序结构关系同不经常候一时这种关涉是情理之中的:A 具有子级 B,然后删除了 A,导致 B 与一条不设有的笔录之间存在关联。倘诺这种作为不得承受,应用程序在剔除父级从前必得先查询其是不是有子嗣。

澳门新萄京官方网站 3

澳门新萄京官方网站 4

用于对支行数据举行索引的国策有二种:

查询钦赐节点的子孙节点:

三,用于HierarchyID数据类型的函数

  • 纵深优先

    纵深优先索引,子树中各行的囤积地方紧邻。比方,一人老总管理的装有雇员都存款和储蓄在其经营的笔录周围。

-- 查询指定节点的子孙节点
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

1,获取当前值的级数(Level)

澳门新萄京官方网站 5

澳门新萄京官方网站 6

调用GetLevel()查看HierarchyID的Level,值是从root节点最初的层数

  • 广度优先

    广度优先将档次结构中每种等第的各行存储在共同。比方,同一COO直属的各雇员的笔录存储在紧邻地点。

回到钦定层级的具有节点:

declare @ha HierarchyID
set @ha=HierarchyID::Parse('/1/2/3/')

select @ha.GetLevel() as Level

澳门新萄京官方网站 7

-- 返回指定层级的所有节点
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 表示要在层次结构中下降的级别数。

2,获取根节点

举个例子上面包车型大巴例子是一个人士表,数占领如下事关:

澳门新萄京官方网站 8

静态方法GetRoot(),静态方法的调用格式:HierarchyID::GetRoot()

Scott

计划新节点:

select HierarchyID::GetRoot().ToString() as TootString,HierarchyID::GetRoot() as RootHierarchyID

|

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

澳门新萄京官方网站 9

Mark <- > Ravi

澳门新萄京官方网站 10

3,重临子节点

| |

本来,那是父节点上边存在着子节点的时候,那么当父节点上面未有子节点应该怎么增多呢?只需求将 GetDescendant(null,null) 的多少个参数都安装为null就行了。如下:

GetDescendant(childleft,childright)用以再次回到父级的贰个子节点,再次回到的子节点和child是同level的。

Ben<-> Laura Vijay <-> Frank <-> James

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
declare @sa Nvarchar(100)
declare @sb Nvarchar(100)
declare @sr Nvarchar(100)
declare @ha HierarchyID
declare @hb HierarchyID
declare @hr HierarchyID

set @sa='/1/2/3/'
set @sb='/1/2/6/'
set @sr='/1/2/'
set @ha=HierarchyID::Parse(@sa)
set @hb=HierarchyID::Parse(@sb)
set @hr=HierarchyID::Parse(@sr)

select  @hr.GetDescendant(null,null).ToString(),
        @hr.GetDescendant(@ha,null).ToString(),
        @hr.GetDescendant(@ha,@hb).ToString()
Use AdventureWorksLT 
Go  
--Scheme Creation 
Create Schema HumanResources 
Go  
--Table Creation 
CREATE TABLE HumanResources.EmployeeDemo 
(
OrgNode HIERARCHYID,
EmployeeID INT,
LoginID VARCHAR(100),
Title VARCHAR(200),
HireDate DATETIME
)
Go 
--Index Creation 
CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo 
ON HumanResources.EmployeeDemo (OrgNode,EmployeeID)

澳门新萄京官方网站 11

澳门新萄京官方网站 12

上面插入一些数据

就算要求在一个父节点上边包车型地铁多个子节点之间插入一个子节点,就要求将 GetDescendant(@Child1,@Child2) 的七个参数同一时候钦点。如下:

假定LeftChild是‘/52%/3’,RightChild是‘/三分之一/4’,须求在那七个节点之间插入贰个新的节点,必要什么样管理?表示节点的数字,并不一定必须是正整数,小数也得以,如下,NewChild=’/54%/3.1/‘;

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  
LUES (hierarchyid::GetRoot(), 1,'adventure-worksscott', 'CEO', '3/11/05') ; 

CLARE @Manager hierarchyid    
LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; 
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)     
LUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-worksMark', 'CTO', '4/05/07')       



CLARE @Manager hierarchyid 
CLARE @FirstChild hierarchyid  
LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; 
lect @FirstChild = @Manager.GetDescendant(NULL,NULL)        
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)        
LUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-worksravi', 'Director Marketing', '4/08/07')        


Insert the First Descendant of a Child Node    
CLARE @Manager hierarchyid     
LECT @Manager = CAST('/1/' AS hierarchyid)       
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
LUES (@Manager.GetDescendant(NULL, NULL),45,   'adventure-worksBen','Application Developer', '6/11/07') ;         


Insert the Second Descendant of a Child Node 
CLARE @Manager hierarchyid        
CLARE @FirstChild hierarchyid         
LECT @Manager = CAST('/1/' AS hierarchyid)   
LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)    

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)      
LUES (@Manager.GetDescendant(@FirstChild, NULL),55,  'adventure-worksLaura','Trainee Developer', '6/11/07') ;           


Insert the first node who is the Descendant of Director Marketing      
CLARE @Manager hierarchyid     
CLARE @FirstChild hierarchyid     
LECT @Manager = CAST('/2/' AS hierarchyid)          

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)   
LUES (@Manager.GetDescendant(NULL, NULL),551, 'adventure-worksfrank','Trainee Sales Exec.', '12/11/07') ;        


Insert the second node who is the Descendant of Director Marketing     
CLARE @Manager hierarchyid           
CLARE @FirstChild hierarchyid        
LECT @Manager = CAST('/2/' AS hierarchyid)   
LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
LUES (@Manager.GetDescendant(@FirstChild, NULL),531, 'adventure-worksvijay','Manager Industrial Sales', '12/09/06') ;         


Insert the third node who is the Descendant of Director Marketing        
in between 2 existing descendants    
CLARE @Manager hierarchyid    
CLARE @FirstChild hierarchyid      
CLARE @SecondChild hierarchyid     
LECT @Manager = CAST('/2/' AS hierarchyid)          
LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)   
LECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)   
SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  
LUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,  'adventure-worksjames','Manager Consumer Sales', '12/04/06') ;           
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
declare @sa Nvarchar(100)
declare @sb Nvarchar(100)
declare @sr Nvarchar(100)
declare @ha HierarchyID
declare @hb HierarchyID
declare @hr HierarchyID

set @sa='/1/2/3/'
set @sb='/1/2/4/'
set @sr='/1/2/'
set @ha=HierarchyID::Parse(@sa)
set @hb=HierarchyID::Parse(@sb)
set @hr=HierarchyID::Parse(@sr)

select    @hr.GetDescendant(null,null).ToString(),
        @hr.GetDescendant(@ha,null).ToString(),
        @hr.GetDescendant(@ha,@hb).ToString()

Hierarchyid字段类型提供了一密密麻麻相关问答函数,能够低价的询问父亲和儿子关周全据。下边大家查询下多少

澳门新萄京官方网站 13

澳门新萄京官方网站 14

DECLARE @TID hierarchyid 
SELECT @TID=OrgNode FROM HumanResources.EmployeeDemo  WHERE title='cto' 

SELECT *, OrgNode.GetLevel() as 层次,OrgNode.ToString() as 路径  FROM HumanResources.EmployeeDemo WHERE @TID.IsDescendantOf(OrgNode)=1 

SELECT *, OrgNode.GetLevel() as 层次,OrgNode.ToString() as 路径  FROM HumanResources.EmployeeDemo WHERE  OrgNode.IsDescendantOf(@TID)=1 

 

改造节点地方:

4,判别七个节点之间的老爹和儿子关系

下面另外附多少个操作的蕴藏进度:

变动节点地方应该利用 GetReparentedValue 方法,该办法接受三个参数,八个是原节点的 hierarchyid,另贰个是指标节点 hierarchyid。

认清是不是是节点的儿孙,child.IsDescendantOf(parent),假设是,重返1,即便不是,重返0

  • 向表里插入记录

    SET QUOTED_IDENTIFIER ON GO --Use Serializable Transaction CREATE PROCEDURE [dbo].AddEmployee, @JobTitle as varchar(200), @JoiningDate datetime) AS BEGIN

    DECLARE @LastChild hierarchyid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo WHERE OrgNode = @ManagerID INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate) COMMIT END ;

  • 移步层级关系

    CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) ) AS BEGIN

    DECLARE @nold HierarchyID DECLARE @nnew HierarchyID

    SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    澳门新萄京官方网站,BEGIN TRANSACTION SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ; SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ; UPDATE HumanResources.EmployeeDemo SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew) WHERE @nold.IsDescendantOf(OrgNode) = 1

    COMMIT TRANSACTION END

-- 把 管理员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
declare @sa Nvarchar(100)
declare @sb Nvarchar(100)
declare @sr Nvarchar(100)
declare @ha HierarchyID
declare @hb HierarchyID
declare @hr HierarchyID

set @sa='/1/2/3/'
set @sb='/1/2/6/'
set @sr='/1/2/'
set @ha=HierarchyID::Parse(@sa)
set @hb=HierarchyID::Parse(@sb)
set @hr=HierarchyID::Parse(@sr)

select    @ha.IsDescendantOf(@hr),
        @hb.IsDescendantOf(@hr),
        @ha.IsDescendantOf(@hb)
  • 得到最大的子节点,传递给GetDescendant() 函数得到新的子节点

    Create Function GetMyMaxChild(@ManagerID as BigInt) Returns HierarchyID BEGIN

    Declare @ManagerNode HierarchyID Declare @MaxChild HierarchyID --Get the ManagerNode Select @ManagerNode = OrgNode from HumanResources.EmployeeDemo Where EmployeeID = @ManagerID --Get the Max Child

    Select @MaxChild = Max(OrgNode) from HumanResources.EmployeeDemo Where OrgNode.GetAncestor(1) = @ManagerNode --Return the Value

    RETURN @MaxChild END

     

澳门新萄京官方网站 15

澳门新萄京官方网站 16

 

四,HierarchyID的值的翻新

hierarchyid 函数:

履新HierarchyID的值,必得级联合土地资金财产更新与该节点相关的子节点的值,那是由于HierarchyID类型自己的局限性导致的。

GetLevel():用于分明当前档案的次序的纵深(品级),最顶层(根节点)为0,然后逐三星1。

HierarchyID数据类型具备以下局限性: 

ToString():因为 hierarchyid 类型的值是以16进制表示的,ToString()用于将 hierarchyid 类型调换为字符串类型。

  • 项目为 HierarchyID的列不会活动表示树。由应用程序来扭转和分配 hierarchyid 值,使行与行之间的所需关系展现在那么些值中。 某个应用程序大概具有 hierarchyid 类型的列,该列提醒在另叁个表中定义的档次结构中的地点。
  • 由应用程序来保管转换和分配 hierarchyid 值时的产出意况。不可能担保列中的 hierarchyid 值是独一的,除非应用程序使用独一键约束或应用程序本人通过自身的逻辑来强制落成独一性。
  • 由 hierarchyid 值表示的档次结构关系不是像外键关系那样强制完结的。  或然晤面世上面这种档期的顺序结构关系何况有的时候这种涉及是在理的:A 具备子级 B,然后删除了 A,导致 B 与一条荒诞不经的记录之间存在涉嫌。 纵然这种行为不得接受,应用程序在剔除父级以前必需先查询其是或不是有后裔

IsDescendantOf():决断钦定节点是或不是是另叁个节点的后代,假如是,则赶回1。多个参数,为钦命的节点。

1,创造数据源

GetAncestor(n):n=0时,会回来当前层级当前节点的数码。不然,会回去钦定层级的下 n 级的持有节点。

create table dbo.emph2
( 
idpath hierarchyid not null primary key,
id int not null,
parentid as idpath.GetAncestor(1) persisted foreign key references dbo.emph2(idpath),
descr varchar(100)
)

GetDescendant(null,null) :用于增多节点,该方法接受五个参数,可为空,第一身形节点,首个子节点。要是第贰个参数不为空,表示在钦命的父节点下边的子节点前面加多节点,假如五个参数皆为空,则意味要在未曾子舆节点的节点增多节点。

澳门新萄京官方网站 17

澳门新萄京官方网站:Hierarchyid数据类型,数据类型用法。GetReparentedValue():用于转移节点地点,该措施接受八个参数,四个是原节点的hierarchyid,另一个是指标节点hierarchyid。

idpath=’/约得其半/6/‘的子孙节点如下图

GetRoot():获取节点的根。

select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
from dbo.emph2 e 
where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1

Parse():将字符串转变为 hierarchyid 。该字符串的格式经常都以/1/这样的。

澳门新萄京官方网站 18

Read():Read 从传播的 BinaryReader 读取 SqlHierarchyId 的二进制表示格局,并将 SqlHierarchyId 对象设置为该值。无法使用 Transact-SQL 调用 Read。请改为使用 CAST 或 CONVERT。

2,把子节点变成另多少个节点的父节点

Write():Write 将 SqlHierarchyId 的二进制表示情势写出到传入的 BinaryWriter 中。无法透过选择Transact-SQL 来调用 Write。请改为使用 CAST 或 CONVERT。

诸如,把idpath=’/二分之一/6/‘ 的节点删除,并将其子节点的父节点改换为idpath=’/三分之一/7/‘

 

是因为存在外键关系,必需先改变子节点的父节点,然后再删除idpath=’/十分之二/6/‘ 的节点。

hierarchyid 索引战略:

--delete child notes
--select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
update e set e.idpath=HierarchyID::Parse('/1/2/7/' cast(e.id as varchar) '/')
from dbo.emph2 e
where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1 and e.idpath!=HierarchyID::Parse('/1/2/6/')

--delete parent note
delete dbo.emph2 where idpath=HierarchyID::Parse('/1/2/6/')

--check
select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
from dbo.emph2 e
where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/7/'))=1

用以对等级次序结构数据举办索引的计划有二种:深度优先和广度优先。

澳门新萄京官方网站 19

深度优先索引,子树中各行的存放地点紧邻,简单来说,就是以 hierarchyid 值排序的章程存款和储蓄。

3,更换父节点

始建深度优先索引:

诸如,把idpath=’/54%/6/‘的节点的父节点改换,其子节点照旧是其子节点。

--创建深度优先索引
create unique index Role_Depth_First 
on RoleMan(NodeId) 

go

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

思路是新建多少个节点,并将子节点都挂在新节点下。

澳门新萄京官方网站 20

--create new node
insert into dbo.emph2(idpath,id,descr)
select HierarchyID::Parse('/1/3/6/'),id,descr
from dbo.emph2 e
where e.idpath=HierarchyID::Parse('/1/2/6/')

--delete child notes
--select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
update e set e.idpath=HierarchyID::Parse('/1/3/6/' cast(e.id as varchar) '/')
from dbo.emph2 e
where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1 and e.idpath!=HierarchyID::Parse('/1/2/6/')

--delete parent note
delete dbo.emph2 where idpath=HierarchyID::Parse('/1/2/6/')

--check
select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
from dbo.emph2 e
where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/3/6/'))=1

广度优先索引,是将等级次序结构中每种级其他各行存款和储蓄在一块,简单的讲,就是按层级排序的法子存款和储蓄。

澳门新萄京官方网站 21

创立广度优先索引:

4,定向插入新的节点

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

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

由于节点之间存在前后相继顺序,使用GetDescendant(ChildLeft,ChildRight)保险顺序。

澳门新萄京官方网站 22

在节点 idpath=’/二分之一/6/‘ 的子节点 id=15,id=16以内插入二个新的子节点,新的子节点的id=36,descr=‘E1136’,思路是选拔GetDescendant(ChildLeft,ChildRight)获取新的IDPath,然后插入到表中。

 

declare @id int
declare @descr Nvarchar(100)
declare @sa Nvarchar(100)
declare @sb Nvarchar(100)
declare @sr Nvarchar(100)
declare @hnew HierarchyID

set @id=36
set @descr='E1136'
set @sa='/1/2/6/15/'
set @sb='/1/2/6/16/'
set @sr='/1/2/6/'

set @hnew= HierarchyID::Parse(@sr).GetDescendant(HierarchyID::Parse(@sa),HierarchyID::Parse(@sb))

insert into dbo.emph2(idpath,id,descr)
values(@hnew,@id,@descr)

select e.idpath.ToString() as IDPath,e.id,e.parentid.ToString() as ParentIDPath,e.descr
from dbo.emph2 e
where e.idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1
order by e.idpath

参考:

澳门新萄京官方网站 23

从排序的结果聚集能够看来,id=36的节点,处于id=15和id=16的节点之间,通过GetDescendant(ChildLeft,ChildRight)达成了逐一。

五, 遍历

澳门新萄京官方网站:Hierarchyid数据类型,数据类型用法。 

HierarchyID类型的数码,很轻便实现广度优先遍历和纵深优先遍历

1,广度优先遍历是指查询档案的次序结构中同样级其余节点

select idpath.ToString() as IDPath,id,parentid.ToString() as ParentIDPath,descr 
from dbo.emph2 where idpath.GetLevel()=2

澳门新萄京官方网站 24

2,深度优先遍历是指遍历叁个节点的全体子节点

select idpath.ToString() as IDPath,id,parentid.ToString() as ParentIDPath,descr 
from dbo.emph2 
where idpath.IsDescendantOf(HierarchyID::Parse('/1/2/6/'))=1

澳门新萄京官方网站 25

 

参照文书档案:

hierarchyid data type method reference

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:Hierarchyid数据类型,数据类

关键词: