SQL Server是关系型数据库，适合存储二维表格的关系型数据，不适合存储具有层次关系的数据，那么如何利用SQL Server存储如下图所示的树型层次关系呢？
目前利用SQL Server存储树型关系数据比较成熟的方案是利用主键+外键的方式，即主键存储一个唯一Id值，外键存储此Id的父节点Id值，如果此节点无父节点，则为null。表设计如下：
Create Database TestDb;
go
use TestDb
go
Create Table EmployeeTable
(Id int primary key,
UserName nvarchar(32) not null ,
ParentId int null references EmployeeTable(Id),
)
go
下面为表填充数据：
insert into EmployeeTable
select 1,'项目经理',null union
select 2,'技术经理',1 union
select 3,'产品经理',1union
select 4,'测试经理',1 union
select 5,'技术组长1',2 union
select 6,'技术组长2',2 union
select 7,'测试员工1',4 union
select 8,'技术员工1',5 union
select 9,'技术员工2',5 union
select 10,'技术员工3',5
go
select * from EmployeeTable
结果集为：
Id UserName ParentId
1 项目经理 NULL
2 技术经理 1
3 产品经理 1
4 测试经理 1
5 技术组长1 2
6 技术组长2 2
7 测试员工1 4
8 技术员工1 5
9 技术员工2 5
10 技术员工3 5
可以看到，项目经理已经是最高级别了，所以它没有父节点，相应的ParentId为null。技术经理、产品经理、测试经理都是项目的子节点，所以它们的ParentId都为1，1即是项目经理的Id。其它节点的插入规则类似。
树型结构是存储下来了，如何高效率的执行递归查询呢？SQL Server 2005以后，提供了CTEs专门用于递归，下面结合上面的例子，给出查询的SQL语句。
1.查询技术组长1所有子节点的员工信息
with c as
(
select * from EmployeeTable where [Id] = 5
union all
select a.* from EmployeeTable as a
join c on a.ParentId = c.Id
)
select * from c
2.查询技术组长1所有父节点的员工信息
with c as
(
select * from EmployeeTable where [Id] = 5
union all
select a.* from EmployeeTable as a
join c on a.Id = c.ParentId
)
select * from c
上述方法试用于SQL Server 2005以上的数据库系统，如果您已经开始使用SQL Server2008了，那么有个好消息要告诉您，从2008开始SQL Server提供了一个新的数据类型hierarchyid，专门用来操作层次型数据结构，hierarchyid相关内容请参考：
http://blog.csdn.net/tjvictor/archive/2009/07/30/4395681.aspx
如需转载，请注明本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
本文主要讲述三个内容：
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中通过主键和外键来存储如下图所示的树型结构数据
虽然通过主键和外键的相互搭配可以满足我们的查询、存储需求，但是这种方式并不易于管理和维护，幸运的是，在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有深度优先索引和广度优先索引
当递归查询父子节点时，会利用到深度优先索引；当平行查询兄弟节点时，会利用到广度优先索引。
深度优先索引图：
广度优先索引图：
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/4395681.aspx
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/tjvictor/archive/2009/07/30/4395681.aspx
浙公网安备 33010602011771号