使用SqlServer CTE递归查询处理树、图和层次结构

CTE（Common Table Expressions）是从SQL Server 2005以后版本才有的。指定的临时命名结果集，这些结果集称为CTE。 与派生表类似，不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。使用CTE能改善代码可读性，且不损害其性能。

递归CTE是SQL SERVER 2005中重要的增强之一。一般我们在处理树，图和层次结构的问题时需要用到递归查询。

CTE的语法如下

 WITH CTE AS
 (
   SELECT EmpId, ReportTo, FName FROM Employ WHERE EmpId=
   UNION ALL
   SELECT emp.EmpId, emp.ReportTo, emp.FName FROM CTE JOIN Employ as emp ON CTE.EmpId=emp.ReportTo
 )

递归CTE最少包含两个查询(也被称为成员)。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。

 USE AdventureWorks;
 GO
 --Creates an infinite loop
 WITH cte (EmployeeID, ManagerID, Title) as
 (
   SELECT EmployeeID, ManagerID, Title
   FROM HumanResources.Employee
   WHERE ManagerID IS NOT NULL
  UNION ALL
   SELECT cte.EmployeeID, cte.ManagerID, cte.Title
   FROM cte
   JOIN HumanResources.Employee AS e
     ON cte.ManagerID = e.EmployeeID
 )
 --Uses MAXRECURSION to limit the recursive levels to
 SELECT EmployeeID, ManagerID, Title
 FROM cte
 OPTION (MAXRECURSION );
 GO

以上内容就是本文给大家介绍的使用SqlServer CTE递归查询处理树、图和层次结构，希望大家喜欢。

赞 (0)

相关推荐

  • 使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法

    使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法

    下面是一个简单的Family Tree 示例: 复制代码 代码如下: DECLARE @TT TABLE (ID int,Relation varchar(25),Name varchar(25),ParentID int) INSERT @TT SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL SELE

  • 在sqlserver中如何使用CTE解决复杂查询问题

    最近,同事需要从数个表中查询用户的业务和报告数据,写了一个SQL语句,查询比较慢: Select S.Name, S.AccountantCode, ( Select COUNT(*) from ( Select Distinct BusinessBackupId from Biz_BusinessBackupCustomer where Id in ( Select BusinessBackupCustomerId from Rpt_RegistForm where ( SignatureCP

  • sqlserver另类非递归的无限级分类(存储过程版)

    sqlserver另类非递归的无限级分类(存储过程版)

    下面是我统计的几种方案: 第一种方案(递归式): 简单的表结构为: CategoryID int(4), CategoryName nvarchar(50), ParentID int(4), Depth int(4) 这样根据ParentID一级级的运用递归找他的上级目录. 还有可以为了方便添加CategoryLeft,CategoryRight保存他的上级目录或下级目录 第二种方案: 设置一个varchar类型的CategoryPath字段来保存目录的完整路径,将父目录id用符号分隔开来.比

  • SQLSERVER2008中CTE的Split与CLR的性能比较

    SQLSERVER2008中CTE的Split与CLR的性能比较

    我们新建一个DataBase project,然后建立一个UserDefinedFunctions,Code像这样: 复制代码 代码如下: 1: /// <summary> /// SQLs the array. /// </summary> /// <param name="str">The STR.</param> /// <param name="delimiter">The delimiter.&l

  • SqlServer使用公用表表达式（CTE）实现无限级树形构建

    SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它.每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存.可以使用CTE来执行递归操作. DECLARE @Level INT=3 ;WITH cte_parent(CategoryID,CategoryName,ParentCategoryID,Level) AS ( SELECT c

  • SQLSERVER2005 中树形数据的递归查询

    问题描述.借用了adinet的问题.参见:http://www.jb51.net/article/28670.htm 今天做项目遇到一个问题, 有产品分类A,B,C顶级分类, 期中A下面有a1,a2,a3子分类. 但是a1可能共同属于A和B,然后我的数据库是这样设计的       id           name         parnet   1 A 0 2 B 0 3 a1 1,2 如果想要查询A的所有子类的话就要查询parent中包含1的,所以就萌生了这个办法.呵呵,解决方案 复制代码

  • 使用SqlServer CTE递归查询处理树、图和层次结构

    CTE(Common Table Expressions)是从SQL Server 2005以后版本才有的.指定的临时命名结果集,这些结果集称为CTE. 与派生表类似,不存储为对象,并且只在查询期间有效.与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次.使用CTE能改善代码可读性,且不损害其性能. 递归CTE是SQL SERVER 2005中重要的增强之一.一般我们在处理树,图和层次结构的问题时需要用到递归查询. CTE的语法如下 WITH CTE AS ( SELECT Em

  • PostgreSQL树形结构的递归查询示例

    背景 处理不确定深度的层级结构,比如组织机构,一个常用的设计是在一张表里面保存 ID 和 Parent_ID ,并且通过自联结的办法构造一颗树.这种方式对写数据的过程很友好,但是查询过程就变得相对复杂.在不引入MPTT模型的前提下,必须通过递归算法来查询某个节点和下级子节点. Oracle提供的connect by扩展语法,简单好用.但是其他的RDBMS就没这么人性化了(或者我不知道).最近在项目中使用PostgreSQL来查询树形数据,记录一下. 构造样本数据 drop table if ex

  • Python解析树及树的遍历

    Python解析树及树的遍历

    解析树 完成树的实现之后,现在我们来看一个例子,告诉你怎么样利用树去解决一些实际问题.在这个章节,我们来研究解析树.解析树常常用于真实世界的结构表示,例如句子或数学表达式. 图 1:一个简单句的解析树 图 1 显示了一个简单句的层级结构.将一个句子表示为一个树,能使我们通过利用子树来处理句子中的每个独立的结构. 图 2: ((7+3)*(5−2)) 的解析树 如图 2 所示,我们能将一个类似于 ((7+3)*(5−2)) 的数学表达式表示出一个解析树.我们已经研究过全括号表达式,那么我们怎样理解

  • 深入理解java三种工厂模式

    适用场合: 7.3 工厂模式的适用场合 创建新对象最简单的办法是使用new关键字和具体类.只有在某些场合下,创建和维护对象工厂所带来的额外复杂性才是物有所值.本节概括了这些场合. 7.3.1 动态实现 如果需要像前面自行车的例子一样,创建一些用不同方式实现同一接口的对象,那么可以使用一个工厂方法或简单工厂对象来简化选择实现的过程.这种选择可以是明确进行的也可以是隐含的.前者如自行车那个例子,顾客可以选择需要的自行车型号:而下一节所讲的XHR工厂那个例子则属于后者,该例中所返回的连接对象的类型取决

  • Java数据结构学习之栈和队列

    Java数据结构学习之栈和队列

    一.栈 1.1 概述 Java为什么要有集合类: 临时存储数据. 链表的本质: 对象间通过持有和引用关系互相关联起来. 线性表: 普通线性表, 操作受限线性表(某些操作受到限制 --> 某一个线性表它的增删改操作受到限制) --> 栈 & 队列 1.1.1 线性表的概念 (1)线性表:n个数据元素的有序序列. ①首先,线性表中元素的个数是有限的. ②其次,线性表中元素是有序的. (2)那这个"序"指的是什么呢? ①除表头和表尾元素外,其它元素都有唯一前驱和唯一后继,

  • java开发模式的深度研究

    目录 一.简单工厂模式 二.工厂方法模式 小结 三.抽象工厂模式 总结 工厂模式主要是为创建对象提供了接口.工厂模式按照<Java与模式>中的提法分为三类: 简单工厂模式(Simple Factory) 工厂方法模式(Factory Method) 抽象工厂模式(Abstract Factory) 这三种模式从上到下逐步抽象,并且更具一般性.还有一种分类法,就是将简单工厂模式看为工厂方法模式的一种特例,两个归为一类.下面是使用工厂模式的两种情况: 1.在编码时不能预见需要创建哪种类的实例. 2

  • 浅谈MySQL表空间回收的正确姿势

    浅谈MySQL表空间回收的正确姿势

    目录 前置说明 问题重现 删除数据原理 数据的复用 哪些操作会造成数据空洞 如何收缩表空间 小结 不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长 为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢? 本文将详细的分析上述问题,并给出正确回收表

  • 基于C++详解数据结构(附带例题)

    基于C++详解数据结构(附带例题)

    目录 前言 数据结构 线性表 顺序存储 链式 小结 栈和队列 栈 后缀表达式 队列 串 串的基本用法 ASCII码 串的基本实现 KMP模式算法匹配 树 树的基本操作 双亲表示法 孩子表示法 孩子兄弟表示法 二叉树 顺序存储 链表存储 遍历二叉树 哈夫曼树 图 顶点 有向图,无向图 顶点和边 连通图 附:如果你还在纠结到底哪门语言作为主语言的话不妨来看看(入门时刻) 最后 前言 应广大支持者的要求,随我自身学习之余,肝数据结构,开车只是暂时的,飙车才是认真的,数据结构开始了,本文用c++编写,如

  • ASP.NET Core配置教程之读取配置信息

    提到"配置"二字,我想绝大部分.NET开发人员脑海中会立马浮现出两个特殊文件的身影,那就是我们再熟悉不过的app.config和web.config,多年以来我们已经习惯了将结构化的配置信息定义在这两个文件之中.到了.NET Core的时候,很多我们习以为常的东西都发生了改变,其中也包括定义配置的方式.总的来说,新的配置系统显得更加轻量级,并且具有更好的扩展性,其最大的特点就是支持多样化的数据源.我们可以采用内存的变量作为配置的数据源,也可以直接配置定义在持久化的文件甚至数据库中. 由

  • Python使用LDAP做用户认证的方法

    LDAP(Light Directory Access Portocol)是轻量目录访问协议,基于X.500标准,支持TCP/IP. LDAP目录以树状的层次结构来存储数据.每个目录记录都有标识名(Distinguished Name,简称DN),用来读取单个记录, 一般是这样的: cn=username,ou=people,dc=test,dc=com 几个关键字的含义如下: base dn:LDAP目录树的最顶部,也就是树的根,是上面的dc=test,dc=com部分,一般使用公司的域名,也

随机推荐