天天看點
傳回

HierarchyID 資料類型用法

樹形層次結構（Hierarchy）經常出現在有結構的資料中，T-SQL新增資料類型HierarchyID， 其長度可變，用于存儲層次結構中的路徑。HierarchyID表示的層次結構是樹形的，由應用程式來生成和配置設定 HierarchyID的值，建立父子節點之間的關系。

HierarchyID資料類型支援深度優先順序的比較，對于兩個HierarchyID值 a和b，a<b意味着，在深度優先周遊時，先周遊到a，後周遊到b，也就是說，值越小，越接近根節點。對Hierarchy資料類型建立索引，是按照深度優先，先左後右的順序來排序的。左和右是根據節點的值來判斷的，在同一深度上，值較小的節點在父節點的左邊。

一，類型的指派

HierarchyID資料類型存儲的是單個節點在樹形結構中的路徑（Path），路徑從根節點（Root Node）開始，根節點是“/”，路徑以“/”結尾，使用整數表示一個節點。這意味着HierarchyID的值必須以“/”開頭，以“/”結尾，“/”之間使用數值（正整數或正小數）辨別一個元素，例如：“/”，“/1/2/”，“/1/2/3/”，"/1/2.1/3"。

有3種指派方式，通過字元串指派，字元串轉換和通過整數指派。

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
HierarchyID 資料類型用法

二，按深度優先順序進行比較 

給定兩個 hierarchyid 值 a 和 b，a<b 表示在對樹進行深度優先周遊時，先找到 a，後找到 b。hierarchyid  資料類型的索引按深度優先順序排序，在深度優先周遊中相鄰的節點的存儲位置也相鄰。同級别的節點，左邊節點小于右邊節點，表示左邊先被周遊到。

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資料類型的函數

1，擷取目前值的級數（Level）

調用GetLevel()檢視HierarchyID的Level，值是從root節點開始的層數

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

select @ha.GetLevel() as Level

2，擷取根節點

靜态方法GetRoot()，靜态方法的調用格式：HierarchyID::GetRoot()

select HierarchyID::GetRoot().ToString() as TootString,HierarchyID::GetRoot() as RootHierarchyID
HierarchyID 資料類型用法

3，傳回子節點

GetDescendant(childleft,childright)用以傳回父級的一個子節點，傳回的子節點和child是同level的。

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()
HierarchyID 資料類型用法

如果LeftChild是‘/1/2/3’，RightChild是‘/1/2/4’，需要在這兩個節點之間插入一個新的節點，需要如何處理?表示節點的數字，并不一定必須是正整數，小數也可以，如下，NewChild=’/1/2/3.1/‘；

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 資料類型用法

4，判斷兩個節點之間的父子關系

判斷是否是節點的後代，child.IsDescendantOf(parent)，如果是，傳回1，如果不是，傳回0

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)
HierarchyID 資料類型用法

四，HierarchyID的值的更新

更新HierarchyID的值，必須級聯地更新與該節點相關的子節點的值，這是由于HierarchyID類型自身的局限性導緻的。

HierarchyID資料類型具有以下局限性： 

  • 類型為 HierarchyID的列不會自動表示樹。由應用程式來生成和配置設定 hierarchyid 值，使行與行之間的所需關系反映在這些值中。 某些應用程式可能具有 hierarchyid 類型的列，該列訓示在另一個表中定義的層次結構中的位置。
  • 由應用程式來管理生成和配置設定 hierarchyid 值時的并發情況。不能保證列中的 hierarchyid 值是唯一的，除非應用程式使用唯一鍵限制或應用程式自身通過自己的邏輯來強制實作唯一性。
  • 由 hierarchyid 值表示的層次結構關系不是像外鍵關系那樣強制實作的。  可能會出現下面這種層次結構關系而且有時這種關系是合理的：A 具有子級 B，然後删除了 A，導緻 B 與一條不存在的記錄之間存在關系。 如果這種行為不可接受，應用程式在删除父級之前必須先查詢其是否有後代

1，建立資料源

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)
)
HierarchyID 資料類型用法

idpath=’/1/2/6/‘的子孫節點如下圖

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
HierarchyID 資料類型用法

2，把子節點變成另一個節點的父節點

例如，把idpath=’/1/2/6/‘ 的節點删除，并将其子節點的父節點變更為idpath=’/1/2/7/‘

由于存在外鍵關系，必須先變更子節點的父節點，然後再删除idpath=’/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/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
HierarchyID 資料類型用法

3，變更父節點

例如，把idpath=’/1/2/6/‘的節點的父節點變更，其子節點仍然是其子節點。

思路是建立一個節點，并将子節點都挂在新節點下。

--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
HierarchyID 資料類型用法

4，定向插入新的節點

由于節點之間存在先後順序，使用GetDescendant(ChildLeft,ChildRight)保證順序。

在節點 idpath=’/1/2/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
HierarchyID 資料類型用法

從排序的結果集中可以看出，id=36的節點，處于id=15和id=16的節點之間，通過GetDescendant(ChildLeft,ChildRight)實作了順序。

五， 周遊

HierarchyID類型的資料，很容易實作廣度優先周遊和深度優先周遊

1，廣度優先周遊是指查詢層次結構中相同級别的節點

select idpath.ToString() as IDPath,id,parentid.ToString() as ParentIDPath,descr 
from dbo.emph2 where idpath.GetLevel()=2
HierarchyID 資料類型用法

2，深度優先周遊是指周遊一個節點的所有子節點

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

參考文檔：

hierarchyid data type method reference

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。

tsql hierarchyid
上一篇: 不再迷惑，無值和NULL值
下一篇: 大資料操作：删除和去重

繼續閱讀