利用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 departmentleft 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将会更加简单方便

轉自:http://blog.csdn.net/LeON382/archive/2007/10/11/1820245.aspx