oracle递归查询

关键字：start...with...connect by prior...   ， 递归查询

 

1.基本用法

select … from tablename

start with 条件1

connect by prior条件2

where 条件3;

 

比较正式的解释是这样的：

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

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

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

 

2.使用方法

          我个人觉得话，首先想要理解语句的话，需要弄清楚这些单词的真正含义，因为如果直译的话，有时候会难以理解它。像这个prior可以翻译成“在前面的”。

           我们通过一个例子来说明，常见的用法，是用来遍历含有父子关系的表结构中。比如省市关系，一个省下面包含多个城市，城市下面有村镇，我们就可以使用这个关键字。

         

3.案例

有这样一样表

table Country

id   parentId    name

1    null             江苏省

2     1                南京市

3     1                苏州市

4     2                栖霞区

5     2                江宁区

6    3                 吴中区

7    3                 吴江区

 

那么，其实在表中，可以看成是一个树状结构：

                                  江苏省

                 南京市                           苏州市

           栖霞区   江宁区            吴中区    吴江区

 

那么如果我们要查询南京市下所有的区的话，可以这么写

select  name  from Country

start with  praentId = '2'

connect by prior  praentId = id

如果想要查询全江苏范围内所有的省市和区县，可以把start with改为praent = '1'

其实这个递归查询咋一听好像还有点复杂，其实很简单，它其实等价于

select  * from Country where praentId = id and praentId = '2'

效果是一样的，但是目前表中只有三层的父子关系，如果有4层以及4层以上，那么查询语句就可能会很复杂并且不好理解。

4.用法详解

    1）.加where字句，尤其要注意where字句的位置，不能放到connect by prior的末尾。

select  name  from Country

where 条件

start with  praentId = '2'

connect by prior  praentId = id

  2）.start with 和 connect by prior 位置可以互换，这个很神奇，例如：

select  name  from Country

connect by prior  praentId = id

start with  praentId = '2'

两者是等价的。

3）. start with 可以省略。

select  name  from Country

connect by prior  praentId = id；

这样查询出来的结果默认是把整张表遍历一遍，用树状图来说明一下，即一棵树有根节点和叶子节点，而叶子节点又可以当做新的根节点继续有新的叶子节点，就像子孙后代一样，无穷无尽。个人感觉用处不大，既然查了整张表，那还不如直接select * from 表不就好了，实际场景中用的比较少。

4.注意点

       在后面跟where 字句一定需要注意的地方在于where字句的作用域， where条件是与与递归查询无关.只是对整个结果起过滤作用。那么如果我们需要where条件来过滤刷选呢？那么我们需要改变sql语句的优先级顺序，可以加一个子查询。先对表进行where字句过滤。例如：

select  name  from （select * from Country where 条件）

start with  praentId = '2'

connect by prior  praentId = id

5.总结

其实递归查询最大的用处就是查询某张存在树状结构的表，刚开始理解有点绕，但是用多了，你会发现这个关键字在查询比较复杂的树状关系表的时候真的是比较简洁明了的，而且既然作为oracle 的关键字，oracle底层也一定对其性能做过优化，查询速度应该很很好。可以留个印象，下次碰到类似的表就可以用啦

 

转载于:https://my.oschina.net/u/3860506/blog/1813115