HierarchyID应用 （新特性）
/**********************************************************
Author="WZ"
Create Date="2008/9/7"
SQL SERVER 2008 开发系列（十三）HierarchyID应用 （新特性）
**********************************************************/
USE Blog
GO
--创建 employee 表用于存放雇员的信息
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE employee
(
EmployeeID int identity(1,1) NOT NULL,
EmpName varchar(20) NOT NULL,
Title varchar(20) NULL,
Salary decimal(18, 2) NOT NULL,
hireDate datetimeoffset(0) NOT NULL,
)
GO
delete from employee
go
--下面的语句将插入公司雇员的数据
INSERT INTO employee
VALUES('任盈盈', '圣姑', 35900.00, '2000-05-23T08:30:00-08:00')
INSERT INTO employee
VALUES('蓝凤凰', '教主', 14000.00, '2002-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES('岳不群', '武林盟主', 35900.00, '2002-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES('令狐冲', '堂主', 14000.00, '2007-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES('宜清', '子弟', 8000.00, '2003-05-23T09:00:00-08:00')
GO
select * from employee
go
--结果
/*
EmployeeID EmpName Title Salary hireDate
----------- ---------------- --------------- ----------------- -------------
1 任盈盈 圣姑 35900.00 2000-05-23 08:30:00 -08:00
2 蓝凤凰 教主 14000.00 2002-05-23 09:00:00 -08:00
3 岳不群 武林盟主 35900.00 2002-05-23 09:00:00 -08:00
4 令狐冲 堂主 14000.00 2007-05-23 09:00:00 -08:00
5 宜清 子弟 8000.00 2003-05-23 09:00:00 -08:00
*/
--上面的雇员之间是有等级关系的,如何表现他们之间的关系呢?
--1:原来我们最原始的做法,增加一个ManagerID上级ID；
--2：我们可以利用SQL SERVER 2008 里的新数据类型HIERARCHYID 表现之间的等级关系；
--使用 hierarchyid 重建数据库
DELETE FROM Employee
GO
ALTER TABLE employee ADD OrgNode hierarchyid NOT NULL
GO
DECLARE @child hierarchyid, --子结点
@Manager hierarchyid = hierarchyid::GetRoot() --根结点，hierarchyid::GetRoot()函数获取根节点
--第一步是在树的顶端添加节点，岳不群-五岳盟主
--所以他的节点就是根节点
INSERT INTO employee
VALUES('岳不群', '五岳盟主', 35900.00,
'2000-05-23T08:30:00-08:00', @Manager)
--下一步是添加直接汇报给岳不群的雇员纪录
SELECT @child = @Manager.GetDescendant(NULL, NULL)
INSERT INTO employee
VALUES('令狐冲', '堂主', 14000.00,
'2002-05-23T09:00:00-08:00', @child)
SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES('宜清', '子弟', 14000.00,
'2002-05-23T09:00:00-08:00', @child)
SELECT @child = @Manager.GetDescendant(null, NULL)
INSERT INTO employee
VALUES('岳灵珊', '堂主', 14000.00,
'2007-05-23T09:00:00-08:00', @child)
GO
--查询
select * from employee
go
DECLARE @manager hierarchyid --子结点
--添加汇报给岳灵珊的雇员
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmpName = '岳灵珊'
INSERT INTO employee
VALUES('小林子', '子弟', 8000.00,
'2003-05-23T09:00:00-08:00', @manager)
GO
--查询雇员信息
SELECT EmpName, Title, Salary, OrgNode.ToString() AS OrgNode
FROM employee ORDER BY OrgNode
GO
--谁汇报给岳灵珊？
DECLARE @岳灵珊 hierarchyid
SELECT @岳灵珊 = OrgNode
FROM employee WHERE EmpName = '岳灵珊'
SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @岳灵珊
--查找直接给岳不群汇报的下级
DECLARE @岳不群 hierarchyid
SELECT @岳不群 = OrgNode
FROM employee WHERE EmpName = '岳不群'
SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @岳不群
GO
--查找所有层次为2的节点
SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetLevel() = 2
GO
--移动节点
DECLARE @宜清 hierarchyid
SELECT @宜清 = OrgNode
FROM employee WHERE EmpName = '宜清'
DECLARE @令狐冲 hierarchyid
SELECT @令狐冲 = OrgNode
FROM employee WHERE EmpName = '令狐冲'
DECLARE @岳不群 hierarchyid
SELECT @岳不群 = OrgNode
FROM employee WHERE EmpName = '岳不群'
Update employee set orgNode=@宜清.GetReparentedValue(@岳不群,@令狐冲)
where OrgNode=@宜清
go
SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
go
/*由hierarchyid 数据类型提供的方法
GetAncestor 返回代表该 hierarchyid 节点第 n 代前辈的 hierarchyid。
GetDescendant 返回该 hierarchyid 节点的子节点。
GetLevel 返回一个整数，代表该 hierarchyid 节点在整个层次结构中的深度。
GetRoot 返回该层次结构树的根 hierarchyid 节点。静态。
IsDescendant 如果传入的子节点是该 hierarchyid 节点的后代，则返回 true。
Parse 将层次结构的字符串表示转换成 hierarchyid 值。静态。
GetReparentedValue 将层次结构中的某个节点移动另一个位置。
ToString 返回包含该 hierarchyid 逻辑表示的字符串。
*/
