锋利的SQL2014：层次结构操作之Hierarchyid

 摘自作者新书《锋利的SQL》（第2版），网购京东：http://item.jd.com/11692900.html

在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 A