利用SQL 2005 CTE处理树型数据

利用SQL 2005 CTE处理树型数据

    在现实生活中,树型数据屡见不鲜:组织机构,产品结构,人事关系等等......记得在以往的一个项目中,涉及到机构,人员,在对其进行处理,特别是进行统计,聚集操作的时候,我一直没找到一个好的方法.临时表,视图,程序控制...能用的办法都用上了,但在处理效率上一直不尽如人意.归根结底,我的感觉就是在数据库中,对数据的操作方式(不管是SQL语言,SP,VIEW...)不象程序语言那样灵活,高效率.
    CTE(Common Table Expression)是SQL SERVER 2005众多新特性之一.他是一个可以由定义语句引用的临时命名结果集.他给我的感觉就是视图,只不过使用起来更加简单(不用在Create View,Drop View之类).更重要的,是他的递归功能,这也正是CTE真正威力所在.下面就给出一个简单的例子.
    在这个想象情形中,有类似如下的机构图:
   
    在数据库中,建立两个表:Department(机构,部门表)和Employee(员工表).表结构和测试数据如下:
    Department表:
   
    Employee表:
   
    现假设如下情形:需要统计各部门(公司)人数的信息(当一个人属于A部门,A部门属于B公司,则在统计A部门和B公司时,都要把该人计算在内.).现在,我们就可以利用CTE强大的递归功能:

 
 

  
  with
  
   EmployCTE (DepartmentID)

  
  as
  
   (
    
  
  --
  
  base case(Anchor member).Get all employee
  
  

  
      
  
  select
  
   DepartmentID 
  
  from
  
   Employee
    
    
  
  union
  
   
  
  all
  
  
    
  
  --
  
  recursive member
  
  

  
      
  
  select
  
   depart.ParentID
    
  
  from
  
   Department depart
    
  
  join
  
   EmployCTE emp 
  
  on
  
   depart.DepartmentID
  
  =
  
  emp.DepartmentID 
    
  
  where
  
   depart.ParentID 
  
  is
  
   
  
  not
  
   
  
  null
  
  
)

  
  select
  
   
  
  *
  
   
  
  from
  
   Department depart 
  
  --
  
  join all department
  
  

  
  left
  
   
  
  join
  
   
(
    
  
  select
  
   EmployCTE.DepartmentID,
  
  count
  
  (
  
  *
  
  ) 
  
  as
  
   EmployeeSum
    
  
  from
  
   EmployCTE
    
  
  left
  
   
  
  join
  
   department depart 
  
  on
  
   depart.DepartmentID
  
  =
  
  EmployCTE.DepartmentID
    
  
  group
  
   
  
  by
  
   EmployCTE.DepartmentID
) empSum 
  
  on
  
   depart.DepartmentID 
  
  =
  
   empSum.DepartmentID
 
 

    通过使用CTE,统计每一个机构(部门)的人数信息现在只用一句SQL语句就搞定了,是不是很令人激动呢:)现在,我们来看一看这条语句:首先,统计的思路是从每一个人开始,得到一个人所在的机构,然后递归的得到该机构的父机构直到顶级机构(类似于这个人所属机构的一个路径).然后将这个人和路径上每个机构关联起来.所有,在SQL中,先定义一个CTE----EmployCTE,再定义这个CET递归的基(在这个例子中,是所有的员工).然后在递归部分,用部门表去连接这个CTE本身(他们之间的关联当然就是DepartmentID了.这样,就得到了所有员工所属机构的路径.然后,在通过以部门为单位,做一次聚集,就得到了所有机构的人数统计信息.(说得舌头都卷了,其实直接看SQL,很是简单明了:).
    顺便,假如,统计每个部门的人数时候,不按照递归来统计(假如一个人属于A部门,A部门属于B公司,则统计B公司时候不统计此人).利用CTE也是相当简单:

 
 

  
  with
  
   DepartmentHierarchy (DepartmentID,DepartmentName,ParentID,HierarchyLevel)

  
  as
  
   (
    
  
  --
  
  base case
  
  

  
      
  
  select
  
   DepartmentID,DepartmentName,ParentID,
  
  1
  
   
  
  as
  
   HierarchyLevel
    
  
  from
  
   Department 
    
  
  where
  
   parentID 
  
  is
  
   
  
  null
  
  

    
  
  union
  
   
  
  all
  
  
    
  
  --
  
  recursive member
  
  

  
      
  
  select
  
   depart.DepartmentID,depart.DepartmentName,depart.ParentID,departHier.HierarchyLevel 
  
  +
  
   
  
  1
  
   
  
  AS
  
   HierarchyLevel
    
  
  from
  
   Department depart
    
  
  inner
  
   
  
  join
  
   DepartmentHierarchy departHier 
    
  
  on
  
   depart.ParentID
  
  =
  
  departHier.DepartmentID 
),
DepartAndEmp(DepartmentID,DepartmentName,ParentID,HierarchyLevel,num)

  
  as
  
   (
    
  
  select
  
   
  
  *
  
   ,EmployeeNum
  
  =
  
  (
  
  select
  
   
  
  count
  
  (
  
  *
  
  ) 
  
  from
  
   Employee 
  
  where
  
   DepartmentID
  
  =
  
  DepartmentHierarchy.DepartmentID)
    
  
  from
  
   DepartmentHierarchy
)

  
  select
  
   
  
  *
  
   
  
  from
  
   DepartAndEmp
 
 

    在上面这个例子中,用到了两个CTE,在使用多个CTE时,只用逗号将各个CTE分开即可.
    另外,在使用CTE的过程中,也感觉到一些不方便的东西.例如:要想使用一个CTE,必须申明了他之后马上使用,否则,其后任何使用,会报"Invalid object"错误.有时我使用完某个CTE后,想再使用一次,麻烦就出来了,郁闷.也不知道MS是怎么想的还是我没找他其他办法?还有,在CTE的递归部分,不能使用诸如SUM,COUNT的聚集函数,这着实让我郁闷,不知道是处于实现上的原因还是性能上的原因.如果能使用的话,在做统计聚集的时候CTE将会更加简单方便.