环境:
- 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设计的表很像!