学步园

本文主要讲述三个内容：
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返回父级的一个子节点