在8.6.4节介绍了使用递归CTE查询层次结构数据的方法，本节将介绍一种使用hierarchyid数据类型解决此问题的方法。Hierarchyid数据类型是从SQL Server2008开始提供的，专门用于解决层次结构问题。
hierarchyid使用“/”符号来表示层次结构，如顶层（根节点）为“/”，其后的子节点可以是“/1/”、“/2/”等。再之后的节点可以是“/1/1/”、“/2/1/”，其中，“/1/1/”的父级是“/1/”，“/2/1/”的父级是“/2/”。在表中，这种层次结构是以16进制方式存储的。
hierarchyid实际上是CLR数据类型，因此与其他数据类型不同的是，它具有一些进行层次节点检索的方法，如表19-26所示。
表19-26 hierarchyid的方法
stu_name |
exam_date |
child.GetAncestor(n) |
返回child的第n层节点的hierarchyid。 |
parent.GetDescendant(child1 , child2) |
返回作为父节点后代的一个子节点hierarchyid。 如果父级为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，则引发异常。 |
node.GetLevel( ) |
用于确定当前层次的深度（级别），最顶层（根节点）为0，然后依次加1。 |
hierarchyid::GetRoot( ) |
返回最顶层（根节点）的hierarchyid。 |
child.IsDescendantOf ( parent ) |
用于判断child节点是否是parent的后代，如果是，返回1；否则，返回0。 |
node.ToString( )与node.Parse( ) |
在表中，hierarchyid是以16进制方式表示的。ToString( )用于将16进制转换为表示层次结构的字符串，Parse( )则反之。 |
node.GetReparentedValue ( oldRoot, newRoot ) |
用于通过将节点从oldRoot移动到newRoot来修改层次树。 |
首先使用下面的代码来创建示例表。注意，其中的Org_Level属于计算列，它使用GetLevel方法计算Org_Node中的当前层次的深度。在插入层次数据时，可以直接使用“/1/”形式的字符串，SQL Server会自动转换存储为16进制格式。
IFOBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
Org_Node hierarchyid NOT NULL,
EmployeeId INT NOT NULL,
Title VARCHAR(50) NOT NULL,
Org_Level AS Org_Node.GetLevel()
);
GO
--插入员工数据,注意第一个列的格式，用于说明层级结构，以/开始和结束
INSERT INTO dbo.EmployeesVALUES
('/',1,'总经理'),
('/1/',2,'副总经理A'),
('/2/',3,'副总经理B'),
('/1/1/',4,'部门经理A'),
('/1/2/',5,'部门经理B'),
('/1/1/1/',6,'员工A');
执行下面的语句，查看dbo.Employees中数据，可以看到Org_Node列中以16进制存储的节点数据，如表19-27所示。
SELECT *,
Org_Node.ToString() AS Org_Node_Str
FROM dbo.Employees;
表19-27 dbo.Employees中的数据
Org_Node |
EmployeeId |
Title |
Org_Level |
Org_Node_Str |
0x |
1 |
总经理 |
0 |
/ |
0x58 |
2 |
副总经理A |
1 |
/1/ |
0x68 |
3 |
副总经理B |
1 |
/2/ |
0x5AC0 |
4 |
部门经理A |
2 |
/1/1/ |
0x5B40 |
5 |
部门经理B |
2 |
/1/2/ |
0x5AD6 |
6 |
员工A |
3 |
/1/1/1/ |
19.8.1 检索祖先节点
IsDescendantOf方法可以判断指定节点是否是另一个节点的后代，如果是，则返回1。下面的示例包含两个步骤，第一条SELECT语句用于获得EmployeeId为4雇员（即部门经理A）的hierarchyid，将其存储在@EmpNode变量中。@EmpNode的数据类型为hierarchyid，因此可以使用IsDescendantOf方法。第二条SELECT语句则是根据获得hierarchyid（即“/1/1/”），从表中检索“/1/1/”是其后代的节点。
DECLARE @EmpNode AShierarchyid;
SELECT @EmpNode = Org_Node
FROM dbo.Employees
WHERE EmployeeId = 4;
SELECT *,Org_Node.ToString() AS Org_Node_Str
FROM dbo.Employees
WHERE@EmpNode.IsDescendantOf(Org_Node) = 1;
查询结果如表19-28所示。
表19-28 部门经理A的祖先节点
Org_Node |
EmployeeId |
Title |
Org_Level |
Org_Node_Str |
0x |
1 |
总经理 |
0 |
/ |
0x58 |
2 |
副总经理A |
1 |
/1/ |
0x5AC0 |
4 |
部门经理A |
2 |
/1/1/ |
19.8.2 检索子树节点
将上面第二条SELECT语句中@EmpNode和Org_Node调换一下位置，就可以检索指定节点的子树节点。下面的语句判断@EmpNode中的节点是否为当前Org_Node的祖先节点，即检索“/1/1/”节点的子树节点。
DECLARE @EmpNode AS hierarchyid;
SELECT @EmpNode = Org_Node
FROM dbo.Employees
WHERE EmployeeId = 4; --节点为“/1/1/”
SELECT *,Org_Node.ToString() AS Org_Node_Str
FROM dbo.Employees
WHERE Org_Node.IsDescendantOf(@EmpNode)= 1;
查询结果如表19-29所示。
表19-29 部门经理A的子树节点
Org_Node |
EmployeeId |
Title |
Org_Level |
Org_Node_Str |
0x5AC0 |
4 |
部门经理 |