T-SQL的树形层次结构 Hierarchy

树形层次结构 Hierarchy

本文参考了Docs\SQL\Reference\Transact-SQL (T-SQL) Reference\hierarchyid methods (database engine)
文档中的内容, 增加了一些举例来进一步说明HierarchyID类型的使用.

树形层次结构 Hierarchy，T-SQL新增数据类型HierarchyID，其长度可变，用于存储层次结构中的路径。
HierarchyID表示的层次结构是树形的，由应用程序来生成和分配 HierarchyID 的值，建立父子节点之间的关系。

HierarchyID 数据类型支持[深度]优先顺序的比较，对于两个HierarchyID值 a 和 b，
a<b意味着，在深度优先遍历时，先遍历到a，后遍历到b。
也就是说，值越小，越接近根节点。
对Hierarchy数据类型创建索引，是按照深度优先，先左后右的顺序来排序的。左和右是根据节点的值来判断的，在同一深度上，值较小的节点在父节点的左边。

01. 定义

  DECLARE @ha HierarchyID;
  DECLARE @hb HierarchyID;
  DECLARE @hc HierarchyID;

02. 类型的赋值

HierarchyID数据类型存储的是单个节点在树形结构中的路径 Path，
路径从根节点 Root Node 开始，根节点是"/"，路径以"/“结尾，使用整数表示一个节点。
这意味着HierarchyID的值必须以”/“开头，以”/“结尾，”/“之间使用数值（正整数或正小数）标识一个元素，例如：”/"，"/1/2/"，"/1/2/3/"，"/1/2.1/3"。

有3种赋值方式，通过字符串赋值，字符串转换和通过整数赋值。

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

03. 取值

select
  @ha as ha,
  @hb.ToString() as hb,
  @hc.ToString() as hc
---------------------------
ha      hb      hc
0x5B5E  /1/2/3/ /1/2/3/
---------------------------

04. 按深度优先顺序进行比较

给定两个 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,'>=','<')

结果: <和<,表示ha<hb,hb<hc

05. 用于HierarchyID数据类型的函数

◆ 获取当前值的级数Level
select @hc.GetLevel() as Level
结果为 3
◆ 获取根节点
静态方法GetRoot()，静态方法的调用格式：HierarchyID::GetRoot()
select HierarchyID::GetRoot().ToString() as RootString,HierarchyID::GetRoot() as RootHierarchyID

    select HierarchyID::GetRoot().ToString() as RootString,HierarchyID::GetRoot() as RootHierarchyID
    ----------------------------
    RootString  RootHierarchyID
    /           0x
    ----------------------------

◆ 返回子节点
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() hr1,
            @hr.GetDescendant(@ha,null).ToString() ha1,
            @hr.GetDescendant(@ha,@hb).ToString() hc1
    ----------------------------------
    hr1       ha1        hc1
    /1/2/1/   /1/2/4/    /1/2/4/
    ----------------------------------

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