【转】sql语句递归查询（start with）

转自：https://blog.csdn.net/hello_a_z/article/details/80053024

 

简介

START WITH CONNECT BY PRIOR这个语法主要用于查询数据包中的树型结构关系。

select * from std_class sc start with  sc.id='xxx' (初始化条件)  connect by  sc.id = prior sc.parent_node_id

prior在后，标识从底部向上递归查根节点。

select * from std_class sc start with  sc.id='xxx' (初始化条件)  connect by prior sc.id = sc.parent_node_id

prior在前，表示从初始条件下开始，递归查找子节点。
————————————————

https://www.cnblogs.com/lirun/p/9116337.html

oracle的start with connect by prior是根据条件递归查询"树"，分为四种使用情况：　

第一种：start with 子节点ID='...' connect by prior 子节点ID = 父节点ID

1	select * from mdm_organization o start with o.org_code='10000008' connect by prior o.org_code=o.org_parent_code

　按照条件org_code='10000008'，对'10000008'（包括自己）及其子节点进行递归查询，结果如下

   查询结果自己所有的后代节点（包括自己）。

第二种：start with 子节点ID='...' connect by 子节点ID = prior 父节点ID

1	select * from mdm_organization o start with o.org_code='10000008' connect by o.org_code=prior o.org_parent_code

　按照条件org_code='10000008'，对'10000008'（包括自己）及其父节点进行递归查询，结果如下

　查询结果自己所有的前代节点（包括自己）。

第三种：start with 父节点ID='...' connect by prior 子节点ID = 父节点ID

1	select * from mdm_organization o start with o.org_parent_code='10000008' connect by prior o.org_code=o.org_parent_code

　按照条件org_parent_code='10000008'，对'10000008'（不包括自己）子节点进行递归查询，结果如下

 

　查询结果自己所有的后代节点（不包括自己）。

第四种：start with 父节点ID='...' connect by 子节点ID = prior 父节点ID

1	select * from mdm_organization o start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_code

按照条件org_parent_code='10000008'，对'10000008'（包括自己）的第一代孩子们及其父节点进行递归查询，结果如下

　查询结果自己的第一代后节点和所有的前代节点（包括自己）。

　如果有where 条件，如下

　select * from mdm_organization o where 条件 start with o.org_parent_code='10000008' connect by o.org_code = prior o.org_parent_code

　执行顺序为先执行start with connect by prior，然后再按照where条件进行过滤。

 

============================================================================

 

在做项目中遇到一个问题，就是同一个表中的数据存在级联关系，但是只要查出来的末级数据，纠结了好久，好不容易找到了一个博主的分享，在这里做个记录，也是和大家一起分享学习一下这位大神的技术，共勉

 

写代码时碰到要弄清楚Oracle的role之间的传递关系,就是有role A的话,可以通过grant A to B,把A赋予给B,又通过grant B to C .那我想知道所有role中,有哪些role具有A的权限.

上网一查发现有个递归查询,不过都讲的不是太详细,而那Oracle整的那用法实在太怪异了,跟我们平时用的SQL差的太远,所以琢磨了好一阵子脑子才转过弯呢.

树形结构

可能一看到递归查询这样太专业的名词大家就迷糊了.实际上可以看成有一个树形结构,然后我们要怎么把所有树的所有结点查找出来.学数据结构的时候我们知道要遍历一个树结构有啥前序遍历,中序遍历,后序遍历.反正挺麻烦的.不像遍历个数组那么容易的.那实际上在Oracle的一个表中也可以保存树形结构信息.你要查询所有的树节点,自己整个函数或存储过程去整肯定是超级麻烦的.Oracle提供了一个简单的机制帮助你.要用到start with ...connect by等关键字.先来假定有下面这样一个简单的树形结构存储在表中.

create table Tree(son char(10), father char(10)); 然后插入些信息变成这样的表

 

SON            FATHTER

孙子SB         儿子

孙子NB         儿子

儿子             爸爸

爸爸             爷爷

 

很显然这是一个简单的树形结构

                           孙子SB

                             ^

                             |

爷爷 --> 爸爸 --> 儿子 -->孙子NB

                          

 

递归查询

假如要查询出以爷爷为根的树的所有节点值咋整呢 ?如果数据少多来几个where嵌套就行.但要是树层次有几百那会搞死人了啊.于是我们就用Oracle提供的递归查询.先看下SQL的写法然后再讲解

SELECT  son FROM tree

START WITH father = '爷爷'

CONNECT BY PRIOR son = father;

返回的结果为 爸爸 儿子 孙子NB 孙子SB

代码看起来很短,但是极为怪异,脑子半天都不容易转过弯呢.实际上我们不把这个SQL语句跟一般的SQL类比,而把它当作给一些函数指定一些参数的赋值语句才更容易理解.

那怎么来理解上面的SQL呢?

首先把SELECT son FROM tree还是看成一般sql一样,就是要查找son这一列的信息.而把以START WITH开头的后面所有东东看成一个where限制条件.其中START WITH 是指定

树的根,这里指定的根是 '爷爷',实际上你还可以指定多个根的,比如 father in ('爷爷', '爸爸') .

而CONNECT BY PRIOR son = father相当于表明在递归的过程中,查找到的树中其它节点接着又作为根结点.然后继续递归. 反正看这sql语句前先想下树形结构,然后想下一般编程语言中的递归函数.再来看时就容易理解点.实际上我觉得Oracle这样设计不太好.如果用户只是简单的指定的一个根节点然后知道树中其他节点信息.那么就直接用START WITH指定根就行了.CONNECT BY PRIOR显得有点多余,可以不用用户去指定.当作一个默认值,只有需要其他一些更复杂的操作时才让用户明确指定.这样就不容易误导人了.

为了便于理解可以可以CONNECT BY那一行当作多余的,只记住要查询的列名放前面,根列名放等号后面就行.这样只要指定树的根结点就比较好理解了.

 

start with ,connect by prior其他变形

上面讲的用START WITH 指定树的根,然后用CONNECT BY指定递归条件.是最简单的也是最常用的形式.但实际上还有些变形.

1.START WITH 可以省略

比如

SELECT son FROM tree

CONNECT BY PRIOR son = father;

此时不指定树的根的话,就默认把Tree整个表中的数据从头到尾遍历一次,每一个数据做一次根,然后遍历树中其他节点信息.

在这个例子中,上面的SQL等价于

SELECT son FROM tree

START WITH father IN (爷爷,爸爸,儿子,孙子NB,孙子SB)

CONNECT BY PRIOR son = father;

那查询到的结果如下,有很多重复信息的

爸爸,儿子,孙子NB,孙子SB    儿子,孙子NB,孙子SB    孙子NB,孙子SB

 

2.START WITH 与CONNECT BY PRIOR位置可互换

 

SELECT son FROM tree

CONNECT BY PRIOR son = father

START WITH father = '爷爷';

这语句与最开头那个是等价的

 

3.nocycle关键字

我们知道标准的树结构中是不会有环的,但表中的树形结构不是标准的,有可能导致环的出现

比如

             ---------孙子SB

             |              ^

             |              |

爷爷 --> 爸爸 --> 儿子 -->孙子NB

哎在这里想用线条整个箭头出来真他妈麻烦啊.我又有点懒不想用其他画图工具啥的啊.反正假设儿子的儿子是孙子SB ,而孙子SB的儿子是爸爸.这样就形成一个环了.

当然在Oracle中的role是禁止出现循环的.比如你grant A to B ,grant B to C .再来个grant C to A会出错的.

假如有个上面的环,在再使用开始的递归查询语言会出错.得用nocycle关键字指定忽略环.

SELECT son FROM tree

START WITH father = '爷爷'

CONNECT BY NOCYCLE PRIOR son = father;

此时结果是

爸爸 儿子 孙子NB

你会注意到由于忽略了环,所以孙子SB的信息也被忽略掉了.

 

4. connect by prior 后面条件顺序的改变 (???)

SELECT son FROM tree

START WITH father = '爷爷'

CONNECT BY PRIOR son = father;

这是开头的写法,但实际上也可以写成father = son倒过来写.有人说没倒过来是从上到下,从根往下.如果倒过来则是从下到上.哎不过我测试了下发现不是那么回事.结果也有点乱七八糟的.没想明白是啥规律.反正还有待研究啊

 

5.还可以加where条件

我上面说了可以把start with ,connect 假装看成where 条件一样.所以在这个sql语句其他地方还可以加其他where 语句,可以看成与递归查询无关.只是对整个结果起过滤作用

比如

SELECT son FROM tree  WHERE son = '孙子SB'

START WITH father = '爷爷'

CONNECT BY PRIOR son = father;

 

当然你不能在最后部分加where,不能在connect by最后面再加.

转自：https://blog.csdn.net/weiwenhp/article/details/8218091

 

==========================================================================

我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中，而这些部门互相有隶属关系。这个时候你就会用到connect by prior start with。

 

connect by 是结构化查询中用到的，其基本语法是：

　　select ... from tablename start with 条件1

　　connect by prior 条件2

　　where 条件3;

　　例：

　　select * from table

　　start with org_id = 'HBHqfWGWPy'

　　connect by prior org_id = parent_id;

　　简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:

　　org_id,parent_id那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。

　　用上述语法的查询可以取得这棵树的所有记录。

　　其中：

　　条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

　　条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。

　　条件3 是过滤条件，用于对返回的所有记录进行过滤。

 

例如:

没有加中start with ... connect by prior ...的查询结果:

select t.dim_id, t.pid, level

from pmcode.pmcode_fj_tree_rl t

where t.dim_id in (select b.dim_id

                      from pmcode.PMCODE_KPI_DIM_OD b

                     where b.kpi_id = 'KC0011')

结果:

DIM_ID PID LEVEL

---------------------

1024 5003 0

1070 0 0

5003 1070 0

5006 0 0

------------------------------------------------------------------------------------

增加start with ... connect by prior ...以后的结果:

select t.dim_id, t.pid, level

from pmcode.pmcode_fj_tree_rl t

where t.dim_id in (select b.dim_id

                      from pmcode.PMCODE_KPI_DIM_OD b

                     where b.kpi_id = 'KC0011')

start with t.dim_id = '1070' ----表示从dim_id = '1070'开始(也就是说1070为根节点)

connect by prior t.dim_id = t.pid; ----表示上条记录的dim_id等于本条记录的pid

结果:

DIM_ID PID LEVEL

---------------------

1070 0 1

5003 1070 2

1024 5003 3