文档章节

递归查询

 卡卡斯
发布于 2017/09/04 10:57
字数 1368
阅读 15
收藏 0
点赞 0
评论 0

SQL递归查询(SqlServer/ORACLE递归查询)[语法差异分析]

在 SQLSERVER2005以后,mssql开始有了递归查询的方法了。比较起最开始写存储过程或者写function的方式。这样的方式更加简便灵活的。

而oracle也有自带的树形结构递归查询方法,connect by

下面我自己写的一段SQL,简单注释下CTE共用表达式的一些用法。 实现对树状结构的根节点和子节点的查询。

 

 

复制代码

代码

------------------------------------------------------------------------
-- author:jc_liumangtu(【DBA】小七)
-- date:    2010-03-30 15:09:42
-- version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
--     Oct 14 2005 00:33:37 
--     Copyright (c) 1988-2005 Microsoft Corporation
--     Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
-- 
------------------------------------------------------------------------
use test
set nocount on
if object_id('Dept','U') is not null
drop table Dept
go
create table Dept(ID int,ParentID int,Name varchar(20))   
insert into Dept select 1,0,'AA' 
insert into Dept select 2,1,'BB' 
insert into Dept select 3,1,'CC'  
insert into Dept select 4,2,'DD'  
insert into Dept select 5,3,'EE'  
insert into Dept select 6,0,'FF' 
insert into Dept select 7,6,'GG' 
insert into Dept select 8,7,'HH' 
insert into Dept select 9,7,'II' 
insert into Dept select 10,7,'JJ' 
insert into Dept select 11,9,'KK' 
 
go   
SELECT * FROM Dept;

--查询树状结构某节点的上级所有根节点。
with cte_root(ID,ParentID,NAME)
as
(
    --起始条件
    select ID,ParentID,NAME
    from Dept
    where Name = 'II'   --列出子节点查询条件
    union all
    --递归条件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join 
    cte_root b          --执行递归,这里就要理解下了 
    on a.ID=b.ParentID  --根据基础表条件查询子节点(a.ID),通过CTE递归找到其父节点(b.ParentID)。
)                       --可以和下面查询子节点的cte_child对比。
select * from cte_root ;

--查询树状结构某节点下的所有子节点。
with cte_child(ID,ParentID,NAME)
as
(
    --起始条件
    select ID,ParentID,NAME
    from Dept
    where Name = 'II' --列出父节点查询条件
    union all
    --递归条件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join 
    cte_child b
    on ( a.ParentID=b.ID)  --根据查询到的父节点(a.Parent),通过CTE递归查询出其子节点(b.ID)
)

select * from cte_child --可以改变之前的查询条件'II'再测试结果


ID          ParentID    Name
----------- ----------- --------------------
1           0           AA
2           1           BB
3           1           CC
4           2           DD
5           3           EE
6           0           FF
7           6           GG
8           7           HH
9           7           II
10          7           JJ
11          9           KK

ID          ParentID    NAME
----------- ----------- --------------------
9           7           II
7           6           GG
6           0           FF

ID          ParentID    NAME
----------- ----------- --------------------
9           7           II
11          9           KK

复制代码

复制代码

 

 

 在msdn中介绍了CTE的一些限制:

至少有一个定位点成员和一个递归成员,当然,你可以定义多个定位点成员和递归成员,但所有定位点成员必须在递归成员的前面 
定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符,最后一个定位点成员与递归成员之间必须使用UNION ALL,递归成员之间也必须使用UNION ALL连接 
定位点成员和递归成员中的字段数量和类型必须完全一致 
递归成员的FROM子句只能引用一次CTE对象 
递归成员中不允许出现下列项 
    SELECT DISTINCT 
    GROUP BY 
    HAVING 
    标量聚合 
    TOP 
    LEFT、RIGHT、OUTER JOIN(允许出现 INNER JOIN) 
    子查询

 接下来介绍下Oracle里面的递归查询方法,connect by prior ,start with。相对于SqlServer来说,Oracle的方法更加简洁明了,简单易懂。很容易就让人理解其用法。借来我会用和上面SqlServer同样的数据和结构进行代码演示,和对一些关键字的用法进行阐述。

 

SELECT …..

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH];

下面是代码测试:

 

复制代码

代码

--创建表
create table Dept(ID int,ParentID int,Name varchar(20));
--增加测试数据,和上面的SqlServer数据相同
insert into Dept  select 1,0,'AA' from dual;
insert into Dept  select 2,1,'BB' from dual;
insert into Dept  select 3,1,'CC'  from dual;
insert into Dept  select 4,2,'DD'  from dual;
insert into Dept  select 5,3,'EE'  from dual;
insert into Dept  select 6,0,'FF' from dual;
insert into Dept  select 7,6,'GG' from dual;
insert into Dept  select 8,7,'HH' from dual;
insert into Dept  select 9,7,'II' from dual;
insert into Dept  select 10,7,'JJ' from dual;
insert into Dept  select 11,9,'KK' from dual;
commit;

--查询根节点(父节点)
select * from Dept            --查询基础表
connect by id=prior parentid  --connect by就是字段的关联关键字,prior有预先和前的意思,则是放在哪个字段前,哪个就是递归的上一层
start with name='II';         --start with则是递归的起始位置,也可以用id或者是parentid。可以修改II的值测试其他数据。

--查询结果
ID    PARENTID    NAME
9      7            II
7      6            GG
6      0            FF

--查询子节点

select * from Dept 
connect by prior id=parentid  --同样的语句,仅仅改变prior位子,就发生了指向性的变化,就是这里id为递归上一层。
start with name='II';

--查询结果
ID    PARENTID    NAME
9       7            II
11    9            KK

--测试结果和SqlServer一致,语句却更精练,简洁易懂。

复制代码

复制代码

 

 

经过分别对SqlServer和Oracle的测试,发现两个数据库都很好的支持递归查询,相比之下Oracle的递归查询语句更加简练易懂,更容易让人理解。

在做测试的时候,SqlServer更方便的产生测试数据,上面的代码可以复制后重复执行,而Oracle复制执行一次可以,重复执行的话,在执行创建表的工作,就会报错了,原因很简单,Oracle要判断表存在然后删除后重建的工作用代码实现很麻烦。而SqlServer只需要if后drop表再create就搞定。所以两种数据库各有千秋。

© 著作权归作者所有

共有 人打赏支持
粉丝 0
博文 7
码字总数 0
作品 0
静安
SQL Server 2008中的CTE递归查询

今天基本搞清楚SQL Server中递归查询的实现方式,So,先记录下来。不过呢,个人觉得SQL Server的递归查询相对于Oracle中的递归查询更加难以理解。 从SQL Server 2005开始,我们可以直接通过C...

cyper ⋅ 2014/05/27 ⋅ 0

SQL SERVER中的递归查询,父节点和其下的子节点

今天基本搞清楚SQL Server中递归查询的实现方式,So,先记录下来。不过呢,个人觉得SQL Server的递归查询相对于Oracle中的递归查询更加难以理解。 从SQL Server 2005开始,我们可以直接通过C...

writeademo ⋅ 2016/07/27 ⋅ 0

PgSQL · 实战经验 · 分组TOP性能提升44倍

业务背景 按分组取出TOP值,是非常常见的业务需求。 比如提取每位歌手的下载量TOP 10的曲目、提取每个城市纳税前10的人或企业。 传统方法 传统的方法是使用窗口查询,PostgreSQL是支持窗口查...

阿里云RDS-数据库内核组 ⋅ 2016/07/04 ⋅ 0

基于Hadoop生态圈的数据仓库实践 —— 进阶技术(七)

七、递归 数据仓库中的关联实体经常表现为一种“父—子”关系。在这种类型的关系中,一个父亲可能有多个孩子,而一个孩子只能属于一个父亲。例如,一个人只能被分配到一个部门,而一个部门可...

wzy0623 ⋅ 2016/07/28 ⋅ 0

Oracle递归查询

--本文章可以直接粘贴到PLSQL运行,测试表就自己创建吧 --测试名称:测试oracle递归 --oracle版本:oracle8i --样表:scoalareatest(地区表) --表结构:国家 level(等级) 1 level决定记录...

lovedreamland ⋅ 2012/11/19 ⋅ 1

T-SQL查询进阶--详解公用表表达式(CTE)

简介 对于SELECT查询语句来说,通常情况下,为了使T-SQL代码更加简洁和可读,在一个查询中引用另外的结果集都是通过视图而不是子查询来进行分解的.但是,视图是作为系统对象存在数据库中,那对...

范大脚脚 ⋅ 2017/12/21 ⋅ 0

oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询

今天发现在oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: select ... from tablename start with cond1 con...

zhengjunbo ⋅ 2013/06/04 ⋅ 0

PostgreSQL的递归查询(with recursive)

开发有需求,说需要对一张地区表进行递归查询,Postgres中有个 with recursive的查询方式,可以满足递归查询(一般>=2层)。 测试如下: 测试语句,查询浙江省及以下县市: 如果查询有报错如死循...

kenyon_君羊 ⋅ 2012/04/25 ⋅ 9

PostgreSQL 递归查询

数据库中的数据存在父子关系(单继承,每一条记录只有一个父亲). 如果要查询一条记录以及他的所有子记录,或者要查询一条记录以及他的所有父记录.那么递归查询就再合适不过了.可以简化复杂的SQL...

kenshiro ⋅ 2013/09/09 ⋅ 1

对于oracle进行简单树查询(递归查询)

对于oracle进行简单树查询(递归查询),很早想总结了,发现了一个,转过来 DEPTID PAREDEPTID NAME NUMBER NUMBER CHAR (40 Byte) 部门id 父部门id(所属部门id) 部门名称 通过子节点向根节点追...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Spring发展历程总结

转自与 https://www.cnblogs.com/RunForLove/p/4641672.html 目前很多公司的架构,从Struts2迁移到了SpringMVC。你有想过为什么不使用Servlet+JSP来构建Java web项目,而是采用SpringMVC呢?...

onedotdot ⋅ 40分钟前 ⋅ 0

Python模块/包/库安装(6种方法)

Python模块/包/库安装(6种方法) 冰颖机器人 2016-11-29 21:33:26 一、方法1: 单文件模块 直接把文件拷贝到 $python_dir/Lib 二、方法2: 多文件模块,带setup.py 下载模块包(压缩文件zip...

cswangyx ⋅ 今天 ⋅ 0

零基础学习大数据人工智能,学习路线篇!系统规划大数据之路?

大数据处理技术怎么学习呢?首先我们要学习Python语言和Linux操作系统,这两个是学习大数据的基础,学习的顺序不分前后。 Python:Python 的排名从去年开始就借助人工智能持续上升,现在它已经...

董黎明 ⋅ 今天 ⋅ 0

openJdk和sun jdk的区别

使用过LINUX的人都应该知道,在大多数LINUX发行版本里,内置或者通过软件源安装JDK的话,都是安装的OpenJDK, 那么到底什么是OpenJDK,它与SUN JDK有什么关系和区别呢? 历史上的原因是,Ope...

jason_kiss ⋅ 今天 ⋅ 0

梳理

Redux 是 JavaScript 状态容器,提供可预测化的状态管理。 它是JS的状态容器,是一种解决问题的方式,所以即可以用于 react 也可以用于 vue。 需要理解其思想及实现方式。 应用中所有的 stat...

分秒 ⋅ 今天 ⋅ 0

Java 后台判断是否为ajax请求

/** * 是否是Ajax请求 * @param request * @return */public static boolean isAjax(ServletRequest request){return "XMLHttpRequest".equalsIgnoreCase(((HttpServletReques......

JavaSon712 ⋅ 今天 ⋅ 0

Redis 单线程 为何却需要事务处理并发问题

Redis是单线程处理,也就是命令会顺序执行。那么为什么会存在并发问题呢? 个人理解是,虽然redis是单线程,但是可以同时有多个客户端访问,每个客户端会有 一个线程。客户端访问之间存在竞争...

码代码的小司机 ⋅ 今天 ⋅ 0

到底会改名吗?微软GVFS 改名之争

微软去年透露了 Git Virtual File System(GVFS)项目,GVFS 是 Git 版本控制系统的一个开源插件,允许 Git 处理 TB 规模的代码库,比如 270 GB 的 Windows 代码库。该项目公布之初就引发了争...

linux-tao ⋅ 今天 ⋅ 0

笔试题之Java基础部分【简】【二】

1.静态变量和实例变量的区别 在语法定义上的区别:静态变量前要加static关键字,而实例变量前则不加。在程序运行时的区别:实例变量属于某个对象的属性,必须创建了实例对象,其中的实例变...

anlve ⋅ 今天 ⋅ 0

Lombok简单介绍及使用

官网 通过简单注解来精简代码达到消除冗长代码的目的 优点 提高编程效率 使代码更简洁 消除冗长代码 避免修改字段名字时忘记修改方法名 4.idea中安装lombnok pom.xml引入 <dependency> <grou...

to_ln ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部