HierarchyID 數據類型用法

来源:https://www.cnblogs.com/ljhdo/archive/2018/01/31/4581349.html
-Advertisement-
Play Games

樹形層次結構（Hierarchy）經常出現在有結構的數據中，T-SQL新增數據類型HierarchyID， 其長度可變，用於存儲層次結構中的路徑。HierarchyID表示的層次結構是樹形的，由應用程式來生成和分配 HierarchyID的值，建立父子節點之間的關係。 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 值 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數據類型的函數

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

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()

如果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()

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 值表示的層次結構關係不是像外鍵關係那樣強制實現的。  可能會出現下麵這種層次結構關係而且有時這種關係是合理的：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)
)

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

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

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

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

從排序的結果集中可以看出，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

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 data type method reference


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • linux常用命令和參數總結（基礎）
    1. 用戶家目錄（~） 在linux系統中，用戶都有一個屬於自己的家目錄，你可以放置自己的配置文件和其他東西 2. pwd：列出當前目錄 可選參數 -P：噹噹前目錄是一個鏈接目錄時，顯示它真正的路徑 3. ls：列出文件和目錄： 可選參數：-a：列出所有文件，包括隱藏文件，即文件以點（.）開頭 -l... ...
  • 嵌入式又有新的發展路線了，你知道嗎？
    摘要：隨著無人機、無人駕駛、可穿戴設備、智能家居、智慧城市等物聯網相關產業技術產品的創新應用及需求的不斷涌現，基礎晶元、軟體、嵌入式系統將被提出更多新的需求和挑戰。而在工業4.0概念下，嵌入式系統將會獲得更廣泛的應用，因為工業4.0概念下的智能工業系統中有大量的電子控制裝置，都是嵌入式系統，如工業機 ...
  • 免費ssl證書申請和在IIS上啟用https的使用教程
    因為微信小程式開發涉及到ssl證書，所以折騰了幾天的這個。 非常感謝”亞洲誠信-TrustAsia“公司的售後工程師黃工（QQ2355718943 TEL:021-58895880-663）提供的技術支持，也非常感謝”亞洲誠信-TrustAsia“公司的商務代表成女士的操作引導。如有看到此文，需要付 ...
  • SuSE的命令安裝軟體 zypper
    轉自：http://blog.csdn.net/s_k_yliu/article/details/6674079 SuSE的命令安裝軟體 zypper，yast2 redhat yum debain apt-gt suse 是 zypper se xxxxx 是搜索軟體包 zypper in xxx ...
  • MYSQL與MSSQL對比學習
    最近在將公司的一個產品裡面相關的MSSQL語句修改為可以在MYSQL上執行的語句 MYSQL與MSSQL語法對比： 其他博客的總結：http://blog.csdn.net/shiqijiamengjie/article/details/50396793 SQL Server 和 Oracle 以及 ...
  • MySQL資料庫常見面試題
    1.事務的特性及隔離級別: 參見博客:http://www.cnblogs.com/Hangtutu/p/8016663.html 2.視圖 視圖（View）是一種虛擬存在的表，對於使用視圖的用戶來說基本上是透明的。視圖並不在資料庫中實際存在，行和列數據來自定義視圖的查詢中使用的表，並且是在使用視圖 ...
  • CentOS下安裝MongoDB
    本文記錄了在CentOS 6或7 x64下安裝MongoDB的方法。 1.配置包管理系統（yum） 以MongoDB 3.6版本為例 創建一個/etc/yum.repos.d/mongodb-org-3.6.repo文件，以便直接使用yum安裝MongoDB； 企業版為/etc/yum.repos. ...
  • Storm WordCount
    特別註意，在本地運行的時候應該去掉<scope>provided</scope>，否則會報java.lang.ClassNotFoundException: org.apache.storm.topology.IRichSpout 集群環境中運行的時候應該加上 在這個例子中，有一個spout，兩個b ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類