sql语句递归查询（startwith）

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

写代码时碰到要弄清楚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 treeSTART 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 = fatherSTART 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 treeSTART WITH father = '爷爷'

CONNECT BY NOCYCLE PRIOR son = father;此时结果是爸爸 ?? 孙?NB

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

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

SELECT son FROM treeSTART 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最后?再加.