sqlserver：数据类型Hierarchyid的介绍和用法

最新推荐文章于 2023-03-29 16:23:58 发布
最新推荐文章于 2023-03-29 16:23:58 发布
阅读量3.8k 收藏 8
点赞数 4
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010476739/article/details/108534193
版权

环境：

  • window10 x64 专业版
  • SqlServer2014 x64 企业版

参照：

一、什么是Hierarchyid？

hierarchyid 是一种长度可变的SqlServer数据类型，在SqlServer2008中被引入进来，它能存储带有层次结构的数据。

1.1 hierarchyid的数据格式

hierarchyid 数据可以和字符串相互转化，所以用字符串表示hierarchyid的格式如下：

select HierarchyID::Parse('/').ToString()
select HierarchyID::Parse('/1/').ToString()
select HierarchyID::Parse('/1/2/3/').ToString()
select HierarchyID::Parse('/124.321.30219/125.-92.80/').ToString()

具体的规则如下：

  • hierarchyid总是以 / 开始和结尾，并且使用 / 划分层次；
  • hierarchyid的根节点是 /；
  • hierarchyid的两个 /之间是用一个或多个 . （不是小数点）分隔的整数串；
  • hierarchyid的比较规则为：
    • 总体按照层级从左到右比较；
    • 同一层级的先用 . 将整数串分开，然后从左到右比较对应的整数；

注意：

分隔的整数串中允许出现前导 - ，但不能出现前导0（单独的0可以）。下面的是不合法的：
'/02/'
'/2/01.2/'
'/1.20.01/'
但下面的是合法的：
'/2/0.2/'
最后再强调下：.只是分隔符，不是小数点。。。

1.2 hierarchyid类型的赋值以及与字符串转换

--定义HierarchyID类型的变量
declare @ha HierarchyID
declare @hb HierarchyID
declare @hc HierarchyID

--直接将字符串赋值给HierarchyID
set @ha='/1/2/3/'
--将字符串解析为HierarchyID
set @hb=HierarchyID::Parse('/1/2/3/')
--直接将16进制数据赋值给HierarchyID
set @hc=0x5B5E

--HierarchyID原始数据,HierarchyID转字符串
select @ha as ha,@hb.ToString() as hb,@hc.ToString() as hc

在这里插入图片描述

1.3 hierarchyid数据的比较示例

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

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

select iif(@ha>=@hb,'>=','<'),iif(@hb>=@hc,'>=','<'),iif(@ha>=@hc,'>=','<')

在这里插入图片描述

1.4 hierarchyid的类型函数

可以从SQL Server Management Studio中查看到以下函数：
在这里插入图片描述

1.4.1 获取当前层级数 GetLevel()

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

set @ha=HierarchyID::Parse('/')
set @hb=HierarchyID::Parse('/1/')
set @hc=HierarchyID::Parse('/1/2.13/')
set @hd=HierarchyID::Parse('/1/2.13/7/')

select @ha.GetLevel(),@hb.GetLevel(),@hc.GetLevel(),@hd.GetLevel()

在这里插入图片描述

1.4.2 获取根节点 GetRoot()

select 
HierarchyID::GetRoot().ToString() as RootString,
HierarchyID::GetRoot() as RootHierarchyID;

在这里插入图片描述

1.4.3 判断两个节点之间的父子关系 IsDescendantOf()

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)

在这里插入图片描述

1.4.4 获取上级 GetAncestor()

declare @sa Nvarchar(100)
declare @ha HierarchyID

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

select  
@ha.GetLevel() as 深度,
@ha.ToString() as 原,
@ha.GetAncestor(1).ToString() as 'GetAncestor(1)',
@ha.GetAncestor(2).ToString() as 'GetAncestor(2)',
@ha.GetAncestor(3).ToString() as 'GetAncestor(3)',
@ha.GetAncestor(4).ToString() as 'GetAncestor(4)'

在这里插入图片描述

1.4.5 获取移动层级关系后的hierarchyid GetReparentedValue()

说明下这个函数（使用伪代码）：
假如存在hierarchyid 为/1/2/3/4/，那么：/1/2/3/4/.GetReparentedValue(/1/2/,/32/)则表示将/1/2/3/4/前缀/1/2/替换为/32/，最后返回/32/3/4/

测试如下：

declare @sa Nvarchar(100)
declare @sb Nvarchar(100)
declare @sr Nvarchar(100)
declare @sd Nvarchar(100)
declare @ha HierarchyID
declare @hb HierarchyID
declare @hr HierarchyID
declare @hd HierarchyID

set @sa='/1/2/3/5/'
set @sb='/1/2/'
set @sr='/3/8/2/'
set @sd='/'
set @ha=HierarchyID::Parse(@sa)
set @hb=HierarchyID::Parse(@sb)
set @hr=HierarchyID::Parse(@sr)
set @hd=HierarchyID::Parse(@sd)

select   
@ha.GetReparentedValue(@sb,@sr).ToString(),
@ha.GetReparentedValue(@sb,@sd).ToString()

在这里插入图片描述

1.4.6 获取两个子级之间可使用的hierarchyid GetDescentant()

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

在这里插入图片描述

1.4.7 将hierarchyid类型数据转为字符串 ToString()

declare @sa Nvarchar(100)
declare @ha hierarchyid

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

select  @ha,@ha.ToString()

在这里插入图片描述

二、怎么使用Hierarchyid？

假设要设计一张部门表记录所有的部门，能满足以下的操作：

  • 快速查询某个部门的所有直接子部门；
  • 快速查询某个部门的所有子孙部门；
  • 快速查询某个部门的同级部门；
  • 快速查询某个部门的一系列父部门；
  • 快速查询总公司下某个层级的所有部门；
  • 新增部门；
  • 删除部门及其所有子孙部门；
  • 移动部门及其子孙部门到其他部门下；

2.1 建表

if OBJECT_ID('t_org') is not null
drop table t_org;
go
create table t_org(
	id int primary key,
	code hierarchyid unique,
	name varchar(50),	
	level as code.GetLevel() persisted,
	pcode as code.GetAncestor(1) persisted
);
go

说明：表t_org的列level和pcode定义为持久化存储的计算列，这样sqlserver会自动计算这两列的值并将结果保存到表中。
建表时，我们需要约定：

  • 部门建立完成后，它的id是不会发生变化的；
  • 部门的层级(code列)是所有上级部门id的有序组合(比如：/1/2/9/表示当前部门的id为9，它的上级部门的id为2，再上级部门的id为9)；
  • 如果同级部门间需要排序的话，可再增加一个排序字段，这里不再演示；

2.2 插入数据

insert into t_org 
values(1,'/1/','总公司')
,(2,'/1/2/','市场部')
,(3,'/1/3/','事业部')
,(4,'/1/4/','综合部')
,(5,'/1/2/5/','销售A组')
,(6,'/1/2/6/','销售B组')
,(7,'/1/3/7/','政府部')
,(8,'/1/3/8/','企事业部')
,(9,'/1/3/9/','零售部')
,(10,'/1/3/9/10/','零售A组')
,(11,'/1/3/9/11/','零售B组')
,(12,'/1/4/12/','人事部')
,(13,'/1/4/13/','财务部');

插入数据后的效果如下：

select id,code.ToString() as code,name,level,pcode.ToString() as pcode from t_org

在这里插入图片描述
它所表示的组织架构图如下：
在这里插入图片描述

2.3 测试查询需求

2.3.1 快速查询某个部门的所有直接子部门

查询事业部(id=3)下的所有直接子节点：

select id,code.ToString() as code,name,level,pcode.ToString() as pcode 
from t_org 
where 
	pcode=(select code from t_org where id=3)

在这里插入图片描述

2.3.2 快速查询某个部门的所有子孙部门

查询事业部(id=3)下的所有子孙部门：

select id,code.ToString() as code,name,level,pcode.ToString() as pcode 
from t_org 
where 
	code.IsDescendantOf((select code from t_org where id=3))=1
	and
	id<>3

在这里插入图片描述

2.3.3 快速查询某个部门的同级部门

查询事业部(id=3)下的所有同级部门：

select id,code.ToString() as code,name,level,pcode.ToString() as pcode 
from t_org 
where pcode=(select pcode from t_org where id=3) and id<>3

在这里插入图片描述

2.3.4 快速查询某个部门的一系列父部门

查询零售部(id=9)的所有父部门：

select id,code.ToString() as code,name,level,pcode.ToString() as pcode 
from t_org 
where (select code from t_org where id=9).IsDescendantOf(code)=1 and id<>9
order by level,id

在这里插入图片描述

2.3.5 快速查询总公司下某个层级的所有部门

查询总公司的所有二级部门：

select id,code.ToString() as code,name,level,pcode.ToString() as pcode 
from t_org 
where level=2

在这里插入图片描述

2.4 测试更改操作

2.4.1 新增部门

零售部门下新增零售C组：

insert into t_org(id,code,name) values(14,'/1/3/9/14/','零售C组');
select id,code.ToString() as code,name,level,pcode.ToString() as pcode from t_org;

在这里插入图片描述

2.4.2 移动部门及其子孙部门到其他部门下

将事业部移动到市场部下面：

移动非叶子节点还是比较复杂的，因为它不仅需要改变当前节点的编码，还需要把所有的子孙节点的编码做修改。不过，一般移动操作是很少见的，我们这里定义一个存储过程操作即可（需要根据节点id查询出要移动的部门和目的部门的编码）。

创建存储过程：

if(OBJECT_ID('usp_move_hierarchy_node') is not null)
drop proc usp_move_hierarchy_node
go
create proc usp_move_hierarchy_node
@tableName varchar(200),--表名称
@colName varchar(200),--hierarchyid所在的列名称
@srcPath hierarchyid, --要移动的节点
@destPath hierarchyid --移动到哪个节点下
as
begin
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION  
	declare @sql nvarchar(1000);
	declare @newPath hierarchyid;
	set @newPath=@srcPath.GetReparentedValue(@srcPath.GetAncestor(1),@destPath);
	set @sql='update '+@tableName+' set '+@colName+'='''+@newPath.ToString()+''' where '+@colName+'='''+@srcPath.ToString()+'''';
	exec sp_executesql @sql;
	set @sql='update '+@tableName+' set '+@colName+'='+@colName+'.GetReparentedValue('''+@srcPath.ToString()+''','''+@newPath.ToString()+''')
	where '+@colName+'.IsDescendantOf('''+@srcPath.ToString()+''')=1';
	exec sp_executesql @sql;
	COMMIT
end

-- 事业部的编码为:'/1/3/',市场部的编码为:'/1/2/'
exec usp_move_hierarchy_node @tableName='t_org',@colName='code',@srcPath='/1/3/',@destPath='/1/2/'
select id,code.ToString() as code,name,level,pcode.ToString() as pcode from t_org;

在这里插入图片描述

2.4.3 删除部门及其所有子孙部门

删除零售部(id=9)及其所有子孙部门：

delete from t_org where code.IsDescendantOf((select code from t_org where id=9))=1;
select id,code.ToString() as code,name,level,pcode.ToString() as pcode from t_org;

在这里插入图片描述

通过以上的实验，可以看到Hierarchyid存储树状数据是比较方便的。

三、附：传统父/子关系表的设计

前面一直在介绍使用Hierarchyid来存储树状结构数据，其实，不使用Hierarchyid我们也是可以存储树状结构数据的，在这里我们可以自行对比一下。

3.1 使用简单的父/子关系设计

同样是存储树状结构数据，使用简单的父/子关系设计建表如下：

if OBJECT_ID('t_org') is not null
drop table t_org;
go
create table t_org(
	id int primary key,
	name varchar(50),
	pid int
);
go
insert into t_org 
values(1,'总公司',null)
,(2,'市场部',1)
,(3,'事业部',1)
,(4,'综合部',1)
,(5,'销售A组',2)
,(6,'销售B组',2)
,(7,'政府部',3)
,(8,'企事业部',3)
,(9,'零售部',3)
,(10,'零售A组',9)
,(11,'零售B组',9)
,(12,'人事部',4)
,(13,'财务部',4);
go
select * from t_org;

在这里插入图片描述

不过，这种设计并没有记录当前部门的层级编码，而这将导致在跨多级查询时异常吃力，但在移动节点时又异常方便。

说明：常见的跨多级查询的解决办法
1.借助CTE实现递归查询（以查询事业部下的所有子孙节点为例）

-- 查询事业部(id=3)下的所有子部门
with cte_org(id,name,pid)as
(
	select * from t_org where id=3
	union all
	select t_org.* from t_org,cte_org where cte_org.id=t_org.pid
)
select * from cte_org where id<>3

在这里插入图片描述
2. 使用递归存储过程、函数以及应用程序内递归的方法
…(略)

3.2 使用改进的父/子关系设计

简单父/子关系表的查询是比较复杂的，而它带来的快速移动节点这个好处的应用频率并不高。基于此，我们可以扩展父/子关系表如下：

if OBJECT_ID('t_org') is not null
drop table t_org;
go
create table t_org(
	id int primary key,
	name varchar(50),
	code varchar(50),
	level int,
	pid int
);
go

说明：
code列是当前部门的所有父级部门的id有序组合而成的，中间以-分隔。

插入测试数据：

insert into t_org 
values(1,'总公司','1',1,null)
,(2,'市场部','1-2',2,1)
,(3,'事业部','1-3',2,1)
,(4,'综合部','1-4',2,1)
,(5,'销售A组','1-2-5',3,2)
,(6,'销售B组','1-2-6',3,2)
,(7,'政府部','1-3-7',3,3)
,(8,'企事业部','1-3-8',3,3)
,(9,'零售部','1-3-9',3,3)
,(10,'零售A组','1-3-9-10',4,9)
,(11,'零售B组','1-3-9-11',4,9)
,(12,'人事部','1-4-12',3,4)
,(13,'财务部','1-4-13',3,4);
go
select * from t_org;

在这里插入图片描述
经过扩展后，它在跨多级查询上变得非常简单，但也带来了移动节点效率低的特点。
而且，你会发现这个扩展后的表和使用Hierarchyid设计的表很像！

jackletter
关注
  • 4
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
省市县三级(SQL Server数据
04-07
省市县三级 SQL Server数据 可以直接运行
mysql hierarchyid_Hierarchyid数据类型
weixin_39815286的博客
01-19 291
以往我们在关系数据库中建立树状结构的时候，通常使用ID+ParentID来实现两条 纪录间的父子关系。但这种方式只能标示其相对位置。解决这类问题在SqlServer2005出现之前通常是采用游标来操作，但熟悉数据库内部机制的人都 知道使用游标带来的性能问题和其他问以往我们在关系数据库中建立树状结构的时候，通常使用ID+ParentID来实现两条 纪录间的父子关系。但这种方式只能标示其相对位置。解决...
SQL HierarchyID 数据类型
weixin_30432179的博客
04-11 211
http://www.cftea.com/c/2009/04/GVQCPXGMHTEC3XEM.asp 我将在本文里向你介绍 SQLServer2008 的一个新特点：HierarchyID 数据类型。我们会看到这个新的数据类型提供了处理树形结构的一个崭新的方法。它增加了 T-SQL 的功能并提升了性能。 本文通过与基于 CTE 的典型方法相比较，深入的介绍了该新的数据类型并给出了一些例子...
使用hierarchyid查询分层数据
寒冰屋的专栏
03-17 674
目录 谁为JoLynn工作？ 使用hierarchyid函数回答 HierarchyID类型内置函数 在本文中，我们将介绍如何执行一个查询以获取层次结构的一部分。对于这个问题，我们将使用AdventureWorks数据库。 谁为JoLynn工作？ Employee表中隐藏的是AdventureWorks的组织结构。虽然HRIS专家擅长基本的SELECT和JOINS，但她很难找到向JoL...
SQL Server 使用 Hierarchyid 操作层次结构数据
qq_18932003的博客
11-23 2391
SQL Server 使用 Hierarchyid 操作层次结构数据 - asdyzh - 博客园 sqlserver：数据类型Hierarchyid的介绍和用法_火焰-CSDN博客 使用EF Core操作层次结构数据 以前我们存储层次结构常用Id+ParentId的方式，例如： Id ParentId Name 1 null 总公司 2 1 分公司1 3 1 分公司2 4 2 部门A 5 4 小组X 6
SQLSERVER实用教程- 表与表数据操作.pdf
最新发布
04-17
### SQL Server实用教程...综上所述，了解表的基本概念、数据类型以及操作方式对于使用SQL Server进行数据管理至关重要。无论是通过界面还是命令方式进行操作，掌握这些基础知识都能帮助开发者更高效地管理和查询数据。
在MS SQL Server中实现多级树
04-11
总结来说，在MS SQL Server中实现多级树结构涉及多种方法，包括自连接、递归查询和使用`hierarchyid`数据类型。每种方法都有其适用场景和优缺点，应根据实际需求和数据库版本来选择合适的方式。在开发过程中，考虑...
sql2008 层次结构id函数 hierarchyid 经典总结
08-05
在SQL Server 2008中，`HierarchyId`是一种特殊的数据类型，用于高效地存储和查询层次结构数据。这种数据类型支持一系列内置方法来处理层次关系，使得在数据库中处理树状结构或层级结构变得更加简单和高效。下面将...
SQL Server中的新数据类型：HierarchyId
hbbsoft的专栏
12-15 550
对于关系型数据库来说，表现树状的层次结构始终是一个问题。微软在Sql Server 2005中首次尝试了解决这个问题，那就是被称之为通用数据表表达式（Common Table Expressions，CTE）的实现方式。尽管CTE在现有的数据库架构中运行良好，微软找到了一种将此类层次结构作为头等概念来使用的方式。因此为了实现这种效果，他们在SQL Server 20
SQL中树形分层数据的查询优化
weixin_34402408的博客
04-08 257
在数据查询中，从2008开始SQL Server提供了一个新的数据类型hierarchyid，专门用来操作层次型数据结构。 hierarchyid  类型对层次结构树中有关单个节点的信息进行逻辑编码的方法是：对从树的根目录到该节点的路径进行编码。  这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列。 表示形式以一条斜杠开头，只访问根的路径由单条斜杠表示。 对于根以下的各级，各标签编...
使用SQL Server 2008中的hierarchyid类型来设计具有树型层次关系的表
灰太狼的博客
07-30 8519
本文主要讲述三个内容： 1.如何创建hierarychyid的表，插入数据及基本递归查询。 2.介绍hierarchyid的10种专有函数。 3.介绍hierarchyid特有的深度优先索引(Depth-First Indexing)和广度优先索引(Breadth-First Indexing) 在上一节中 http://blog.csdn.net/tjvictor/archive/200
SQL SERVER 2008 新数据库类型hierarchyid
dz45693的专栏
06-03 2834
SQL SERVER 2008 新数据库类型hierarchyid
SQL Server 2008中原生的分层数据类型：hierarchyid
dfdfadsf3443的博客
05-03 92
SQL Server 2008中SQL应用系列--目录索引 如果是在SQL 2000中，我们需要读取分层结构数据时，不得不借助递归。在SQL server 2005中，我们可以使用CTE，当然，好的数据结构设计可以避免你使用CTE，请看这里：http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html 如果你是...
数据结构--知识点16--搜索算法(二叉树)
ANingL的博客
09-06 3006
文章目录一、树的概念1、特点2、树的术语3、树的种类4、树的存储与表示5、常见的树的应用场景二、二叉树1、概念2、性质 一、树的概念 树是一种抽象数据类型(ADT)或是视作这种抽象数据类型的数据结构 1、特点 每个节点有零个或多个子节点 没有父节点的节点称为根节点 每一个非根节点有且只有一个父节点 除了根节点外，每个子节点可以分为**多个不相交(因为都只有一个父节点)**的子树 2、树的术语 节点的度：一个节点含有的子树的个数称为该节点的度 树的度：一棵树中，最大的节点的度称为树的度 叶节点或终端节点
SQL Server 2008中的hierarchyid
weixin_34087301的博客
04-26 117
这也是SQL Server 2008的一个重要新增特性。主要解决的问题是拥有层次关系的表格。例如我们日常生活中用到最多的组织结构图。我们一般会用一个Employees表保存员工数据，而每个员工则又可能会有相应的上级。以前要得到某个员工的所有上级，或者所有下级，通常所采取的方法都是递归。SQL Server 2005开始支持的CTE从一定程序上方便了该工作的实现。 但SQL 2008的hierarc...
SqlServer常用函数汇总
qq_46104221的博客
03-29 1149
【代码】SqlServer常用函数汇总。
HierarchyID 数据类型用法
yunnGuitu的博客
01-18 173
树形层次结构（Hierarchy）经常出现在有结构的数据中，T-SQL新增数据类型HierarchyID， 其长度可变，用于存储层次结构中的路径。HierarchyID表示的层次结构是树形的，由应用程序来生成和分配 HierarchyID的值，建立父子节点之间的关系。 HierarchyID数据类型支持深度优先顺序的比较，对于两个HierarchyID值 a和b，a
SQL Server 2008R2 hierarchyid的使用方法（父子节点、上下级处理）
矫正的快乐人生
11-27 2955
SQL Server 2008R2 hierarchyid的使用方法（父子节点、上下级处理） SQL Server 2008 及以上版本，出了这样一个字段类型，用于处理父子节点、员工上下级等问题。使得此类问题变得更容易处理些。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

jackletter

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值