孤独的猫

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

在SQL SERVER 2008中引入了一个新的类型,为hierarchyid,支持层次结构,hierarchyid从技术上讲是一个CLR UDT,但是在SQL SERVER 2008不必先启用它。

考虑下面的层次结构

可以用如下SQL语句表示

CREATE TABLE Employees 
(node hierarchyid PRIMARY KEY CLUSTERED,
 level as node.GetLevel() PERSISTED,
 employee_id INT UNIQUE,
 employee_name varchar(30) NOT NULL)
 
 
insert into Employees Values(hierarchyid::GetRoot(),5000,'Rob') 

declare @ManagerNode hierarchyid
declare @Level hierarchyid

select @ManagerNode=node FROM Employees WHERE employee_id=5000
insert into Employees Values(@ManagerNode.GetDescendant(NULL,NULL),5001,'Bill')

select @Level=node from Employees where employee_id=5001
insert into Employees values(@ManagerNode.GetDescendant(@Level,NULL),5002,'Steve')

select node.ToString() as NodeAsString,node as NodeAsBinary,
  node.GetLevel() As Level,employee_id,employee_name from Employees
  
declare @Level hierarchyid  
select @Level=node from Employees where employee_id=5002
insert into Employees values(@Level.GetDescendant(NULL,NULL),5003,'Jim')  

declare @child1 hierarchyid
select @child1=node from Employees where employee_id=5003
insert into Employees values(@Level.GetDescendant(@child1,NULL),5004,'Steve')

select @Level=node from Employees where employee_id=5001
insert into Employees values(@Level.GetDescendant(NULL,NULL),5005,'Joseph')


select @child1=node from Employees where employee_id=5005
insert into Employees values(@Level.GetDescendant(@child1,NULL),5006,'Joan')


select @Level=node from Employees where employee_id=5006
insert into Employees values(@Level.GetDescendant(NULL,NULL),5007,'Alice')

select @child1=node from Employees where employee_id=5007
insert into Employees values(@Level.GetDescendant(@child1,NULL),5008,'Barbara')

select node.ToString() as NodeAsString,node as NodeAsBinary,
  node.GetLevel() As Level,employee_id,employee_name from Employees
 
 

 

posted on 2012-11-15 21:47  孤独的猫  阅读(652)  评论(0编辑  收藏  举报