利用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公用表表达式 (CTE)的用法和运用场景

sql server 2005开始推出了公用表表达式 (CTE),这个表达式是个人觉得挺有用的。 我主要是用于树结构的递归查询和简化sql语句增加可读性和可维护性。 公用表表达式其实提供的功能...
  • jsjpanxiaoyu
  • jsjpanxiaoyu
  • 2017年01月26日 17:55
  • 1432

SQL Server CTE 递归查询全解

最近工作中遇到了一个问题,需要根据保存的流程数据,构建流程图。数据库中保持的流程数据是树形结构的,表结构及数据如下图: 仔细观察表结构,会发现其树形结构的特点: FFIRSTNODE:标记是否...
  • 3150379
  • 3150379
  • 2017年02月04日 20:09
  • 2311

SQL CTE学习总结

一句SQL完成动态分级查询 http://www.cnblogs.com/powertoolsteam/p/sqlite.html 在最近的活字格项目中使用ActiveReports报表设计器设计一个...
  • bcbobo21cn
  • bcbobo21cn
  • 2017年05月03日 21:12
  • 528

SQL Server 2005 最大容量规范

 SQL Server 2005 数据库引擎对象 最大大小/数量 SQL Server 2005(32 位) 最大大小/数量 SQL Server 2005(64 位) ...
  • cxzhq2002
  • cxzhq2002
  • 2014年05月27日 16:37
  • 766

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

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

sql中with的用法(CTE公用表表达式):应用子查询嵌套,提高sql性能

一.WITH AS的含义 WITH AS短语,也叫子查询部分(subquery factoring),定义一个SQL片断,该片断会被整个SQL语句所用到。有时是为了让SQL语句的可读性更高些,也可能...
  • longshenlmj
  • longshenlmj
  • 2013年07月09日 21:41
  • 1623

SQL Server 2005 导出包含(insert into)数据的SQL脚本 (使用存储过程)

--SQL Server里面导出SQL脚本(表数据的insert语句) CREATE PROCEDURE dbo.UspOutputData  @tablename sysname  AS  ...
  • duanbeibei
  • duanbeibei
  • 2016年11月24日 15:55
  • 779

SQL Server 2005 在局域网中共享数据库——Sql Server 2005的1433端口打开和进行远程连接

http://blog.163.com/kewangwu%40126/blog/static/867284712009101972828708/     如何打开sql server2005的1...
  • zunguitiancheng
  • zunguitiancheng
  • 2014年12月17日 18:04
  • 464

sql server 2005数据库无法读写

客户用的是sql server 2005数据库,在正常使用过程中突然断电,重启服务器后,数据库变为“质疑”,数据库无法读写...
  • tongyuekeji123
  • tongyuekeji123
  • 2016年07月16日 16:49
  • 340

使用Sqlserver事务发布实现数据同步

 使用Sqlserver事务发布实现数据同步     这里以sqlserver2008的事务发布功能为例,对发布订阅的方式简要介绍一下操作流程,一方面做个总结备份,一方面与大家进行一下分享...
  • zhanlurbh
  • zhanlurbh
  • 2014年10月25日 16:36
  • 1380
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:利用SQL 2005 CTE处理树型数据
举报原因:
原因补充:

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