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 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 = '爷爷';
这语句与最开头那个是等价的
我们知道标准的树结构中是不会有环的，但表中的树形结构不是标准的，有可能导致环的出现
⽐如
---------孙⼦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 语句，可以看成与递归查询⽆关.只是对整个结果起过滤作⽤
sql容易学吗⽐如
SELECT son FROM tree WHERE son = '孙⼦SB'
START WITH father = '爷爷'
CONNECT BY PRIOR son = father;
当然你不能在最后部分加where，不能在connect by最后⾯再加.
发表评论