摘自作者新书《锋利的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