SQL Server 的一个重要新增特性主要解决的问题是拥有层次关系的表格例如我们日常生活中用到最多的组织结构图我们一般会用一个Employees表保存员工数据而每个员工则又可能会有相应的上级以前要得到某个员工的所有上级或者所有下级通常所采取的方法都是递归SQL Server 开始支持的CTE从一定程序上方便了该工作的实现
但SQL 的hierarchyid让这个工作更加简化和直接而该类型其实是一个CLR自定义数据类型
一般我们使用的时候如下面的例子
创建表
CREATE TABLE Employees
(
Org_Id hierarchyid NOT NULL
EmployeeId INT NOT NULL
EmployeeName VARCHAR() NOT NULL
Title VARCHAR() NOT NULL
)
GO
插入一些员工注意第一个列的格式必须用/开始和结束这是一个路径的符号这是一个关键
INSERT INTO dboEmployees VALUES(/陈希章CEO);
INSERT INTO dboEmployees VALUES(//张三CTO);
INSERT INTO dboEmployees VALUES(//李四CFO);
INSERT INTO dboEmployees VALUES(///王五IT Manager);
INSERT INTO dboEmployees VALUES(///赵六Manager);
INSERT INTO dboEmployees VALUES(////洪七Employee);
查看所有的员工
SELECT * FROM dboEmployees
查看所有的员工及其级别
SELECT *Org_IdGetLevel() AS Level FROM Employees
查看陈希章的所有下属
DECLARE @BOSS hierarchyid
SELECT @BOSS=Org_Id FROM Employees WHERE EmployeeID=
SELECT *Org_IdGetLevel()AS Level FROM Employees WHERE Org_IdIsDescendantOf(@BOSS)=
查看赵六及其所有上级
DECLARE @Employee hierarchyid
SELECT @Employee=Org_Id FROM Employees WHERE EmployeeID=
SELECT *Org_IdGetLevel()AS Level FROM Employees WHERE @EmployeeIsDescendantOf(Org_Id)=
与hierarchyid有关的一些函数主要有
GetAncestor 取得某一个级别的祖先
GetDescendant 取得某一个级别的子代
GetLevel 取得级别
GetRoot 取得根
IsDescendantOf 判断某个节点是否为某个节点的子代
Parse 将字符串转换为hierarchyid该字符串的格式通常都是//这样的
Read
Read 从传入的 BinaryReader 读取 SqlHierarchyId 的二进制表示形式并将 SqlHierarchyId 对象设置为该值不能使用 TransactSQL 调用 Read请改为使用 CAST 或 CONVERT
GetReparentedValue 可以用来移动节点(或者子树)
ToString 将hierarchyid转换为字符串与parse正好相反
Write
Write 将 SqlHierarchyId 的二进制表示形式写出到传入的 BinaryWriter 中无法通过使用 TransactSQL 来调用 Write请改为使用 CAST 或 CONVERT
