现在的位置: 首页 > 数据库 > 正文

使用SQL Server 2008中的hierarchyid类型来设计具有树型层次关系的表

2017年09月10日 数据库 ⁄ 共 7391字 ⁄ 字号 评论关闭

1674人阅读评论(4)收藏举报

本文主要讲述三个内容:
1.如何创建hierarychyid的表,插入数据及基本递归查询。
2.介绍hierarchyid的10种专有函数。
3.介绍hierarchyid特有的深度优先索引(Depth-First Indexing)和广度优先索引(Breadth-First Indexing)

在上一节中

http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
我们已经演示了如何在SQL Server中通过主键和外键来存储如下图所示的树型结构数据
image

虽然通过主键和外键的相互搭配可以满足我们的查询、存储需求,但是这种方式并不易于管理和维护,幸运的是,在SQL Server 2008中提供了一种新的数据类型hierarchyid和相关的操作方法来存储和查询这种树型层次关系数据。

首先创建数据表:
create database TestDb
go
use TestDb
go
Create table EmployeeTreeTable
(
NodeId hierarchyid PRIMARY KEY,
NodeLevel AS NodeId.GetLevel(),
EmployeeId int UNIQUE NOT NULL,
EmployeeName nvarchar(32) NOT NULL,
)
NodeId是记录树型层次的Id,是hierarchyid类型。NodeLevel是个计算列,用于存储当前树是深度值,根节点为0。关于NodeId.GetLevel()方法将在下面章节中详细介绍。

按照上图所示的层次关系为表插入数据:
--插入数据
declare @DepthNode hierarchyid;--深度Id
declare @BreadthNode hierarchyid;--广度Id
--插入根节点
insert into EmployeeTreeTable values(hierarchyid::GetRoot(),1,'项目经理')
--计算深度并插入子节点2
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 1;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),2,'技术经理');

--计算节点2广度,在节点2右边插入节点3
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),3,'产品经理');

--计算节点3广度,在节点3右边插入节点4
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 3;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),4,'测试经理');

--计算节点2深度并插入子节点5
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),5,'技术组长1');

--计算节点5广度,在节点5右边插入节点6
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 5;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),6,'技术组长2');

--计算节点4深度并插入子节点7
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 4;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),7,'测试员工1');

--计算节点5深度并插入子节点8
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 5;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),8,'技术员工1');

--计算节点8广度,在节点8右边插入节点9
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 8;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),9,'技术员工2');

--计算节点9广度,在节点9右边插入节点10
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 9;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),10,'技术员工3');

go
select * from EmployeeTreeTable
结果集为:
NodeId NodeLevel EmployeeId EmployeeName
0x 0 1 项目经理
0x58 1 2 技术经理
0x5AC0 2 5 技术组长1
0x5AD6 3 8 技术员工1
0x5ADA 3 9 技术员工2
0x5ADE 3 10 技术员工3
0x5B40 2 6 技术组长2
0x68 1 3 产品经理
0x78 1 4 测试经理
0x7AC0 2 7 测试员工1

1.查询技术组长1所有子节点的员工信息
select * from EmployeeTreeTable
where NodeId.IsDescendantOf(0x5AC0)=1--0x5AC0是技术组长1的NodeId

2.查询技术组长1所有父节点的员工信息
with c as
(
select * from EmployeeTreeTable where EmployeeId = 5
union all
select a.* from EmployeeTreeTable as a
join c on a.NodeId = c.NodeId.GetAncestor(1)
)
select * from c

上面的例子中,使用了很多hierarchyid专有的函数,可能大家还不熟悉,下面我将具体介绍一下hierarchyid的10个函数,分别为:
GetRoot,GetLevel,GetAncestor,GetDescendant,IsDescendantOf,ToString,Parse,GetReparentedValue,Read,Write。

1.GetRoot。返回层次结构树的根节点。注意GetRoot() 是静态方法。
关于SQL中静态方法和实例方法的区别请参见:http://blog.csdn.net/tjvictor/archive/2009/07/29/4390673.aspx

SQL:select * from EmployeeTreeTable where NodeId = hierarchyid::GetRoot()
结果集:
NodeId NodeLevel EmployeeId EmployeeName
0x 0 1 项目经理

2.返回一个表示节点在树中的深度的整数。
前面建表时我们已经使用了这个函数,NodeLevel字段就是用这个函数自动创建的。
SQL:select EmployeeName,NodeId.GetLevel() as TreeLevel from EmployeeTreeTable
结果集为:
EmployeeName TreeLevel
项目经理 0
技术经理 1
技术组长1 2
技术员工1 3
技术员工2 3
技术员工3 3
技术组长2 2
产品经理 1
测试经理 1
测试员工1 2

3.GetAncestor返回表示本节点为的第 n 个父节点的 hierarchyid。
SQL:
declare @NodeId hierarchyid
select @NodeId=NodeId from EmployeeTreeTable where EmployeeId = 5
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(0)

select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(1)

select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(2)

结果集为:
EmployeeName NodeLevel
技术组长1 2
技术经理 1
项目经理 0
@NodeId.GetAncestor(0) 取自己节点的Id,@NodeId.GetAncestor(1)取父节点的Id,@NodeId.GetAncestor(2)取爷节点的Id,以此类推。

4.GetDescendant返回父级的一个子节点

  • 如果父级为 NULL,则返回 NULL。
  • 如果父级不为 NULL,而 child1 和 child2 为 NULL,则返回父级的子级。
  • 如果父级和 child1 不为 NULL,而 child2 为 NULL,则返回一个大于 child1 的父级的子级。
  • 如果父级和 child2 不为 NULL,而 child1 为 NULL,则返回一个小于 child2 的父级的子级。
  • 如果父级、child1 和 child2 都不为 NULL,则返回一个大于 child1 且小于 child2 的父级的子级。
  • 如果 child1 不为 NULL 且不是父级的子级,则引发异常。
  • 如果 child2 不为 NULL 且不是父级的子级,则引发异常。
  • 如果 child1 >= child2,则引发异常。
    我们在插入的SQL语句中已经使用过了这个方法,这里就不再给出SQL示例,请大家参考前面的插入SQL语句。

    5.IsDescendantOf如果子节点为本节点的后代,则返回 true
    SQL:select * from EmployeeTreeTable where NodeId.IsDescendantOf(0x58)=1
    结果集为:
    NodeId NodeLevel EmployeeId EmployeeName
    0x58 1 2 技术经理
    0x5AC0 2 5 技术组长1
    0x5AD6 3 8 技术员工1
    0x5ADA 3 9 技术员工2
    0x5ADE 3 10 技术员工3
    0x5B40 2 6 技术组长2

    6.ToString返回具有本节点逻辑表示形式的字符串
    SQL:select *,NodeId.ToString() as Path from EmployeeTreeTable
    结果集为:
    NodeId NodeLevel EmployeeId EmployeeName Path
    0x 0 1 项目经理 /
    0x58 1 2 技术经理 /1/
    0x5AC0 2 5 技术组长1 /1/1/
    0x5AD6 3 8 技术员工1 /1/1/1/
    0x5ADA 3 9 技术员工2 /1/1/2/
    0x5ADE 3 10 技术员工3 /1/1/3/
    0x5B40 2 6 技术组长2 /1/2/
    0x68 1 3 产品经理 /2/
    0x78 1 4 测试经理 /3/
    0x7AC0 2 7 测试员工1 /3/1/

    7.Parse将hierarchyid 的规范字符串表示形式转换为hierarchyid值。即与ToString()函数是相反函数。Parse是静态函数。

    SQL:
    declare @Path varchar(32) = '/1/2/5/6/'
    select hierarchyid::Parse(@Path)
    结果集为:0x5B6394

    8.GetReparentedValue把当前节点从旧路径更新到新路径
    下面的SQL是把技术员工3,从技术组长1节点更新到技术组长2下面。
    SQL:
    declare @OldNode hierarchyid=0x5AC0;
    declare @NewNode hierarchyid=0x5B40;
    update EmployeeTreeTable set NodeId = NodeId.GetReparentedValue(@OldNode,@NewNode)

    where EmployeeId = 10
    结果集中技术员工3的路径从/1/1/3/变成了/1/2/3/。
    关于GetReparentedValue的用法比较复杂,我在介绍索引后,会更加详细的说明各种替换情况。

    9.Read和Write
    Read和Write是供CLS调用的,不能在T-SQL中直接使用。所以这里就不具体介绍两个函数的使用方法了。

    hierarchyid有深度优先索引和广度优先索引
    当递归查询父子节点时,会利用到深度优先索引;当平行查询兄弟节点时,会利用到广度优先索引。
    深度优先索引图:
    depth

    广度优先索引图:
    breadth

    1.建立深度优先索引:
    深度优先索引是hierarchyid默认的索引,只要在hierarchyid列上建立主键,那么就会自动建立hierarchyid索引。

    2.建立广度优先索引
    广度优先索引必须是个唯一索引且包括NodeLevel和NodeId两列:
    CREATE UNIQUE INDEX IX_EmployeeBreadth ON Employee(NodeLevel, NodeId)

    需要注意的是采用深度优先、广度优先还是结合使用这两种索引,以及将哪一种设为聚集键(如果有),取决于上述两种查询类型的相对重要性以及 SELECT 与 DML 操作的相对重要性,本文不代表一定要如此建立hierarchyid索引。

    最后我们讨论一下hierarchyid的GetReparentedValue几种使用方法。
    下面我们先看一个有问题的节点更新:把技术组长1从技术经理更新到产品经理。
    SQL:
    declare @OldNode hierarchyid=0x58;
    declare @NewNode hierarchyid=0x68;
    update EmployeeTreeTable set NodeId = NodeId.GetReparentedValue(@OldNode,@NewNode)

    where EmployeeId = 5
    go
    select NodeId.ToString(),* from EmployeeTreeTable
    结果集为:
    路径 NodeId NodeLevel EmployeeId EmployeeName
    / 0x 0 1 项目经理
    /1/ 0x58 1 2 技术经理
    /1/1/1/ 0x5AD6 3 8 技术员工1
    /1/1/2/ 0x5ADA 3 9 技术员工2
    /1/1/3/ 0x5ADE 3 10 技术员工3
    /1/2/ 0x5B40 2 6 技术组长2
    /2/ 0x68 1 3 产品经理
    /2/1/ 0x6AC0 2 5 技术组长1
    /3/ 0x78 1 4 测试经理
    /3/1/ 0x7AC0 2 7 测试员工1
    从结果里面可以看到技术组长已经变成了/2/1,成功更新到产品经理节点下。但是技术组长1下面的子节点技术员工1,2,3却没有相应的更新过来,还是原来的/1/1/1,2,3,但是原先的技术组长1的/1/1节点已经没有了,所以出现了所谓的“断层”现象。

    下面提出几种常用更新需求,并且给出相应的SQL实现语句。

    1.职位变更。例如技术经理与产品经理职位互换。
    针对这种情况,有两种方法。一是把技术经理下面的所有节点Id都更新成产品经理节点下。这种情况变动比较大,不推荐使用。第二种方法是把技术经理的NodeId和产品经理的NodeId互换。下面使用第二种方法:

    declare @TechNode hierarchyid=0x58;
    declare @ProductNode hierarchyid=0x68;
    declare @TempNode hierarchyid=0x59;
    update EmployeeTreeTable set NodeId = @TempNode where NodeId = @TechNode;
    update EmployeeTreeTable set NodeId = @TechNode where NodeId = @ProductNode;
    update EmployeeTreeTable set NodeId = @ProductNode where NodeId = @TempNode;

    2.职位升降级。例如技术组长2降级成为技术员工,被挂在技术组长1节点下:
    declare @TechTeamLeadNode1 hierarchyid=0x5AC0;
    declare @TechEmployeeNode3 hierarchyid=0x5ADE;
    update EmployeeTreeTable set NodeId = @TechTeamLeadNode1.GetDescendant(@TechEmployeeNode3,null)

    where EmployeeId = 6
    部分结果集为:
    Path NodeId NodeLevel EmployeeId EmployeeName
    /1/1/4/ 0x5AE1 3 6 技术组长2
    可见,技术组长2从/1/2变成了/1/1/4

    总结:
    SQL Server 2008提供的hierarchyid类型使我们能够灵活、方便的操作树型结构。关于hierarchyid还有很多深入的知识,很多灵活的用法,本文不可能一一涉及,这里仅是介绍一些基本用法,抛砖引玉,如果大家在以后的使用中发现什么问题或是更好的解决方案,请联系我。

    如需转帐,请注明本文原创自CSDN TJVictor专栏:http://blog.csdn.net/tjvictor/archive/2009/07/30/43956

  • 抱歉!评论已关闭.