[SQL SERVER]雇佣方法

树状(hierarchy)形结构资料，真实世界中很常见，SQL2005大多都使用CTE递回来达到目的，EF6开始也有支援该资料类型。

SQL2008推出hierarchyid资料类型，该类型长度是可变的，用来储存阶层结构资料，建立父子节点之间的关联。

资料比较是按照深度优先，两个hierarchyID(a和b)，假设a

declare @myhiera HierarchyID
declare @myhierb HierarchyID
declare @myhierc HierarchyID

set @myhiera='/11/21/31/'
set @myhierb=HierarchyID::Parse('/11/21/31/')
set @myhierc=@myhiera

select @myhiera as c1,@myhierb.ToString() as c2,@myhierc.ToString() as c3

我个人常用方法

create table myEmployeeH (
    ID INT UNIQUE NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Node HierarchyID ,
    NodeLevel as Node.GetLevel(),  -- GetLevel 節點深度
    NodeStringPath as (Node.ToString())
    )
create unique index UIX_NodeLevel on myEmployeeH (NodeLevel,Node)
go
GetRoot():靜態方法，回傳樹狀結構的root  
insert into myEmployeeH (ID,Name,Node)
       values (1, 'rico',HierarchyId::GetRoot())

GetDescendant():傳回父系子節點
declare @parent hierarchyid = hierarchyid::GetRoot()
   insert into myEmployeeH (ID,Name,Node) 
   values
   (2,'ricoisme',@parent.GetDescendant(null,null))—參數表示左和右節點
Go
declare @parent hierarchyid = (select Node from myEmployeeH where name = 'rico')
   declare @ricoisme hierarchyid = (select Node from myEmployeeH where name = 'ricoisme')
   insert into myEmployeeH (ID,Name,Node) values (3, 'Fifi',@parent.GetDescendant(@ricoisme,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'rico')
   declare @Fifi hierarchyid = (select Node from myEmployeeH where name = 'Fifi')
   insert into myEmployeeH (ID,Name,Node) values(4, 'Sherry',@parent.GetDescendant(@Fifi,null))
   go

     declare @parent hierarchyid = (select Node from myEmployeeH where name = 'ricoisme')
  insert into myEmployeeH (ID,Name,Node) 
   values
   (5,'r1',@parent.GetDescendant(null,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'ricoisme')
     declare @r1 hierarchyid = (select Node from myEmployeeH where name = 'r1')
  insert into myEmployeeH (ID,Name,Node) 
   values
   (6,'r1-1',@parent.GetDescendant(@r1,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'r1-1')
     insert into myEmployeeH (ID,Name,Node) 
   values
   (7,'r1-1-A',@parent.GetDescendant(null,null))
   go
    declare @parent hierarchyid = (select Node from myEmployeeH where name = 'r1-1')
     declare @r11A hierarchyid = (select Node from myEmployeeH where name = 'r1-1-A')
     insert into myEmployeeH (ID,Name,Node) 
   values
   (8,'r1-1-AB',@parent.GetDescendant(@r11A,null))
   Go
select * from myEmployeeH

目前最大阶层数=3

GetAncestor:代表this的第n阶层

IsDescendantOf ( parent ):若为父系，return true

Parse:标准hierarchyID字串转换成hierarchyID值，作用和ToString相反

--找出階層/1/的子樹狀資料
select [Parent]=(Node.GetAncestor(1).ToString())
,* 
from myEmployeeH f1 
where f1.Node.IsDescendantOf(HierarchyID::Parse('/1/')) = 1

–阶层/1/2/的子树状资料

更新/1/2/节点成为/1/3/和相关子节点

--update parent
   update myEmployeeH set node=HierarchyID::Parse('/1/3/')
   where Node.GetAncestor(0).ToString()='/1/2/'

   --update childs
   update e set node=   
    HierarchyID::Parse( replace( left(Node.ToString(),5),'/1/2/','/1/3/')+ Right(Node.ToString(),len(Node.ToString())-5) )
    --HierarchyID::Parse('/1/2/'+cast(e.id as varchar)+'/')
    from myEmployeeH e
    where Node.IsDescendantOf(HierarchyID::Parse('/1/2/'))=1

--check
select [Parent]=(Node.GetAncestor(1).ToString())
,* 
from myEmployeeH f1 
where f1.Node.IsDescendantOf(HierarchyID::Parse('/1/3/')) = 1

PS:delete parent node资料，预设不会连带删除childe nodes，需要自行处理

和CTE比较一下查询树状结构资料效能

create table myEmployee (
    ID INT UNIQUE NOT NULL,
    Name VARCHAR(50) NOT NULL,
    ParentId Int Null,
    Path varchar(100)
    )
create index IX_ParentId on myEmployee (ParentId)
go

insert into myEmployee values(1,'rico',null,'/')
,(2,'ricoisme',1,'/1/')
,(3,'Fifi',1,'/2/')
,(4,'Sherry',1,'/3/')
,(5,'r1',2,'/1/1/')
,(6,'r1-1',2,'/1/3/')
,(7,'r1-1-A',6,'/1/3/1/')
,(8,'r1-1-AB',6,'/1/3/2/')

set statistics io,time on
declare @Id int = 2;
with FolderHierarchy (id, name, path, parentid, level) as 
(
    select Id, Name, Path, ParentId, 1 as level from myEmployee where Id = @Id
    union all
    select f.Id, f.Name, f.Path, f.ParentId, level + 1 from myEmployee f
    inner join FolderHierarchy h
    on f.ParentId = h.Id
)
select * from FolderHierarchy order by level

  select * from myEmployeeH
   where Node.IsDescendantOf(HierarchyID::Parse('/1/'))=1

文章来自点部落dotblogs.com.tw