• SQL SERVER 2008的层次结构支持


    在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
     
     
  • 相关阅读:
    w3cschoolDjango中文教程
    Golang基本语法2
    w3cschool微信小程序开发文档服务端
    bianchenggo语言概要总结
    Golang语言简介1
    Spring中的@Transactional注解为什么要加rollbackFor = Exception.class之源码解析
    Java 编译期与运行期,别傻傻分不清楚!
    java为什么有些异常throw出去需要在函数头用throws声明,一些就不用?
    @Transactional(rollbackFor=Exception.class)的使用
    Lambda 表达式有何用处?如何使用?
  • 原文地址:https://www.cnblogs.com/djcsch2001/p/2772303.html
Copyright © 2020-2023  润新知