文档章节

递归查询

 卡卡斯
发布于 2017/09/04 10:57
字数 1368
阅读 17
收藏 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中的递归查询,父节点和其下的子节点

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

writeademo
2016/07/27
25
0
SQL Server 2008中的CTE递归查询

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

cyper
2014/05/27
65
0
PgSQL · 实战经验 · 分组TOP性能提升44倍

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

阿里云RDS-数据库内核组
2016/07/04
0
0
T-SQL查询进阶--详解公用表表达式(CTE)

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

范大脚脚
2017/12/21
0
0
基于Hadoop生态圈的数据仓库实践 —— 进阶技术(七)

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

wzy0623
2016/07/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Ubuntu18.04 安装MySQL

1.安装MySQL sudo apt-get install mysql-server 2.配置MySQL sudo mysql_secure_installation 3.设置MySQL非root用户 设置原因:配置过程为系统root权限,在构建MySQL连接时出现错误:ERROR...

AI_SKI
今天
2
0
3.6 rc脚本(start方法) 3.7 rc脚本(stop和status方法) 3.8 rc脚本(以daemon方式启动)

3.6-3.7 rc脚本(start、stop和status方法) #!/usr/bin/env python# -*- coding: utf-8 -*-# [@Version](https://my.oschina.net/u/931210) : python 2.7# [@Time](https://my.oschina.......

隐匿的蚂蚁
今天
3
0
Cnn学习相关博客

CNN卷积神经网络原理讲解+图片识别应用(附源码) 笨方法学习CNN图像识别系列 深度学习图像识别项目(中):Keras和卷积神经网络(CNN) 卷积神经网络模型部署到移动设备 使用CNN神经网络进行...

-九天-
昨天
4
0
flutter 底部输入框 聊天输入框 Flexible

想在页面底部放个输入框,结果键盘一直遮住了,原来是布局问题 Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: Text("评论"), ...

大灰狼wow
昨天
4
0
Kernel I2C子系统

备注:所有图片来源于网络 1,I2C协议: 物理拓扑: I2C总线由两根信号线组成,一条是时钟信号线SCL,一条是数据信号线SDA。一条I2C总线可以接多个设备,每个设备都接入I2C总线的SCL和SDA。I...

yepanl
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部