Oracle递归查询父子兄弟节点

<span style="font-size: 15px"><span style="color: #008080">1</span> <span style="color: #808080">//</span><span style="color: #000000"> 查询id为101的所有后代节点，包含101在内的各级父节点
</span><span style="color: #008080">2</span> <span style="color: #0000ff">select</span> t.<span style="color: #808080">*</span> <span style="color: #0000ff">from</span> SYS_ORG t start <span style="color: #0000ff">with</span> id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">101</span><span style="color: #ff0000">‘</span> connect <span style="color: #0000ff">by</span> parent_id <span style="color: #808080">=</span> prior id</span>

2、查询某节点下所有后代节点（不包含各级父节点）

<span style="font-size: 15px"><span style="color: #008080">1</span> <span style="color: #0000ff">select</span> t.<span style="color: #808080">*</span>
<span style="color: #008080">2</span>   <span style="color: #0000ff">from</span><span style="color: #000000"> SYS_ORG t
</span><span style="color: #008080">3</span>  <span style="color: #0000ff">where</span> <span style="color: #808080">not</span> <span style="color: #808080">exists</span> (<span style="color: #0000ff">select</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">from</span> SYS_ORG s <span style="color: #0000ff">where</span> s.parent_id <span style="color: #808080">=</span><span style="color: #000000"> t.id)
</span><span style="color: #008080">4</span>  start <span style="color: #0000ff">with</span> id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">101</span><span style="color: #ff0000">‘</span>
<span style="color: #008080">5</span> connect <span style="color: #0000ff">by</span> parent_id <span style="color: #808080">=</span> prior id</span>

3、查询某节点所有父节点（所有祖宗节点） 

<span style="font-size: 15px"><span style="color: #008080">1</span> <span style="color: #0000ff">select</span> t.<span style="color: #808080">*</span>
<span style="color: #008080">2</span>   <span style="color: #0000ff">from</span><span style="color: #000000"> SYS_ORG t
</span><span style="color: #008080">3</span>  start <span style="color: #0000ff">with</span> id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">401000501</span><span style="color: #ff0000">‘</span>
<span style="color: #008080">4</span> connect <span style="color: #0000ff">by</span> prior parent_id <span style="color: #808080">=</span> id</span>

4、查询某节点所有的兄弟节点（亲兄弟）

<span style="font-size: 15px"><span style="color: #008080">1</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> SYS_ORG t
</span><span style="color: #008080">2</span> <span style="color: #0000ff">where</span> <span style="color: #808080">exists</span> (<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> SYS_ORG s <span style="color: #0000ff">where</span> t.parent_id<span style="color: #808080">=</span>s.parent_id <span style="color: #808080">and</span> s.id<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">401000501</span><span style="color: #ff0000">‘</span>)</span>

5、查询某节点所有同级节点（族节点），假设不设置级别字段

<span style="font-size: 15px"><span style="color: #008080">1</span> <span style="color: #0000ff">with</span> tmp <span style="color: #0000ff">as</span><span style="color: #000000">(
</span><span style="color: #008080">2</span>       <span style="color: #0000ff">select</span> t.<span style="color: #808080">*</span>, <span style="color: #0000ff">level</span><span style="color: #000000"> leaf        
</span><span style="color: #008080">3</span>       <span style="color: #0000ff">from</span><span style="color: #000000"> SYS_ORG t               
</span><span style="color: #008080">4</span>       start <span style="color: #0000ff">with</span> t.parent_id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span>     
<span style="color: #008080">5</span>       connect <span style="color: #0000ff">by</span> t.parent_id <span style="color: #808080">=</span><span style="color: #000000"> prior t.id)
</span><span style="color: #008080">6</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span>                               
<span style="color: #008080">7</span>       <span style="color: #0000ff">from</span><span style="color: #000000"> tmp                             
</span><span style="color: #008080">8</span> <span style="color: #0000ff">where</span> leaf <span style="color: #808080">=</span> (<span style="color: #0000ff">select</span> leaf <span style="color: #0000ff">from</span> tmp <span style="color: #0000ff">where</span> id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">401000501</span><span style="color: #ff0000">‘</span>);</span>

这里使用两个技巧，一个是使用了level来标识每个节点在表中的级别，还有就是使用with语法模拟出了一张带有级别的临时表

 6、查询某节点的父节点及兄弟节点（叔伯节点）

<span style="font-size: 15px"><span style="color: #0000ff">with</span> tmp <span style="color: #0000ff">as</span><span style="color: #000000">(
    </span><span style="color: #0000ff">select</span> t.<span style="color: #808080">*</span>, <span style="color: #0000ff">level</span><span style="color: #000000"> lev
    </span><span style="color: #0000ff">from</span><span style="color: #000000"> SYS_ORG t
    start </span><span style="color: #0000ff">with</span> t.parent_id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">
    connect </span><span style="color: #0000ff">by</span> t.parent_id <span style="color: #808080">=</span><span style="color: #000000"> prior t.id)  
</span><span style="color: #0000ff">select</span> b.<span style="color: #808080">*</span>
<span style="color: #0000ff">from</span> tmp b,(<span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
            <span style="color: #0000ff">from</span><span style="color: #000000"> tmp
            </span><span style="color: #0000ff">where</span> id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">401000501</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> lev <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">2</span><span style="color: #ff0000">‘</span><span style="color: #000000">) a
</span><span style="color: #0000ff">where</span> b.lev <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>
 
<span style="color: #0000ff">union</span> <span style="color: #808080">all</span>
 
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
<span style="color: #0000ff">from</span><span style="color: #000000"> tmp
</span><span style="color: #0000ff">where</span> parent_id <span style="color: #808080">=</span> (<span style="color: #0000ff">select</span> <span style="color: #0000ff">distinct</span><span style="color: #000000"> x.id
                </span><span style="color: #0000ff">from</span> tmp x, <span style="color: #008080">--</span><span style="color: #008080">祖父</span>
                     tmp y, <span style="color: #008080">--</span><span style="color: #008080">父亲</span>
                     (<span style="color: #0000ff">select</span> <span style="color: #808080">*</span>
                      <span style="color: #0000ff">from</span><span style="color: #000000"> tmp
                      </span><span style="color: #0000ff">where</span> id <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">401000501</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> lev <span style="color: #808080">></span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">2</span><span style="color: #ff0000">‘</span>) z <span style="color: #008080">--</span><span style="color: #008080">儿子</span>
                <span style="color: #0000ff">where</span> y.id <span style="color: #808080">=</span> z.parent_id <span style="color: #808080">and</span> x.id <span style="color: #808080">=</span> y.parent_id);</span> 

这里查询分成以下几步。
首先，将全表都使用临时表加上级别；
其次，根据级别来判断有几种类型，以上文中举的例子来说，有三种情况：
（1）当前节点为顶级节点，即查询出来的lev值为1，那么它没有上级节点，不予考虑。
（2）当前节点为2级节点，查询出来的lev值为2，那么就只要保证lev级别为1的就是其上级节点的兄弟节点。
（3）其它情况就是3以及以上级别，那么就要选查询出来其上级的上级节点（祖父），再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。
最后，就是使用union将查询出来的结果进行结合起来，形成结果集。

Oracle递归查询父子兄弟节点

标签：connect   union   oracl   microsoft   nio   用两个   技巧   body   bsp