利用SQL 2005 CTE处理树型数据

转载 2007年10月11日 15:21:00

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

 

Sql server 2005 CTE 拆分字符串

 alter FUNCTION [dbo].[UF_SpliteIntToTable]  (      @String VARCHAR(8000)  )  RETURNS @Table...
  • soarheaven
  • soarheaven
  • 2010-12-31 15:47:00
  • 566

用SQL Server 2005 CTE简化查询

 SQL Server 2005引进了一个很有价值的新的Transact-SQL语言组件:一个通用表表达式(Common Table Expression,CTE),它是派生表和视图的一个便捷的替代。...
  • jerryjbiao
  • jerryjbiao
  • 2009-11-19 01:45:00
  • 3478

[SQL Server 2005/2008]递归更新update(使用CTE公用表达式)

[SQL Server 2005/2008]递归 更新 update (使用CTE公用表达式), 应用举例, t_user_sells中有每个用户每天的销售量, 现在需要得到每个用户每天的销量增长率...
  • xiaoxu0123
  • xiaoxu0123
  • 2011-03-13 16:03:00
  • 3078

利用SQL 2005 CTE处理树型数据(ZT)

在现实生活中,树型数据屡见不鲜:组织机构,产品结构,人事关系等等......记得在以往的一个项目中,涉及到机构,人员,在对其进行处理,特别是进行统计,聚集操作的时候,我一直没找到一个好的方法.临时表,...
  • zhouhaihe
  • zhouhaihe
  • 2006-08-02 12:37:00
  • 850

SQL数据库CTE的用法

在很多编程语言中都有 for循环这样的东西。在数据库里面 替代他是 游标 但是游标使用起来是相当耗费资源的,今天看见一个CTE尝试了下他的用法create table employewhere( id...
  • jacky4955
  • jacky4955
  • 2010-03-05 11:46:00
  • 3041

SQLServer CTE递归和循环对比的优势--典型案例

首先,我们新建一张测试用的临时表#country,其中包含三个字段,AreaNam(地名) ,BelongTo(上级地名) ,Msg(地方简介)Create table #country (AreaN...
  • Wikey_Zhang
  • Wikey_Zhang
  • 2017-05-23 14:35:03
  • 1108

[sql server]SQL Server2005杂谈(2):公用表表达式(CTE)的递归调用.doc

  • 2010年07月29日 08:57
  • 122KB
  • 下载

SQL Server 2008中的CTE递归查询得到一棵树(按照每颗树的架构排列)

感觉这个CTE递归查询蛮好用的,先举个例子:   [c-sharp] view plain copy  print? use City;   "http://lib.csdn.net...
  • goodyuedandan
  • goodyuedandan
  • 2017-04-17 14:50:38
  • 1405

SQL With (递归CTE查询)

指定临时命名的结果集,这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询,并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CRE...
  • u010796790
  • u010796790
  • 2016-07-08 18:50:01
  • 3213

[sql server] SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL

  • 2010年07月29日 08:55
  • 50KB
  • 下载
收藏助手
不良信息举报
您举报文章:利用SQL 2005 CTE处理树型数据
举报原因:
原因补充:

(最多只允许输入30个字)