Oracle递归查询

--本文章可以直接粘贴到PLSQL运行，测试表就自己创建吧

--测试名称：测试oracle递归  

--oracle版本：oracle8i

--样表：s_coalarea_test（地区表）

--表结构：国家 level（等级） 1 level决定记录在树中的层次

--     省份 level（等级） 2

--     地区 level（等级） 3

--     依次类推

--备注：这张地区表是一颗树，递归查询主要就是方便维护树

--测试1：简单递归

--需求：根据地区名称查询其所有的下级地区名称

--备注：aid是表主键，parentid是父节点主键，areaname地区名称

--分析：递归查询其实还是一个条件过滤

select s.areaname from  s_coalarea_test s 

  start with            --start with 是递归入口，也就是递归的起始位置

s.areaname ='黑龙江'       --指明入口条件 

connect by prior         --prior 前序遍历 指明遍历方向 入不写 默认不递归查询      

s.aid=s.parentid         --级联条件 也就是你的树通过哪个字段相连的 顺序决定查询结果 须注意 

 

--测试2：谈递归入口 start with

--需求：根据多个地区名查询器所有下级地区

select s.areaname from  s_coalarea_test s 

  start with            

  s.areaname ='黑龙江'  --递归入口可以是多个值，其原理：先根据start with过滤记录

or           --然后根据过滤后的记录，依次递归遍历结果

s.areaname='山西' 

connect by prior              

  s.aid=s.parentid  

--测试3：再谈递归入口 

--需求：根据多个地区名查询器所有下级地区

--错误：测试结果让人恼火，原来start with先于where执行，所以这个测试很失败，大家要注意

select s.areaname from  s_coalarea_test s

  where s.areaname='黑龙江'

or

  s.areaname = '山西' 

start with            

    s.areaname = s.areaname --既然start with只是过滤记录，那么当然我们也可以不在此处过滤，而直接用where过滤的结果了

connect by prior              

  s.aid=s.parentid  

 

--测试4：谈级联条件

--需求：根据地区名，查询上级地区

select s.areaname from  s_coalarea_test s

  start with            

    s.areaname='大同'

connect by prior              

  s.parentid=s.aid --注意，此时仅仅是级联条件顺序改变了

         --总结：到底是查父节点还是子节点，有级联顺序决定

         --规律：【本记录字段】=【连接字段】

         --如本实例：是通过本记录的parentid匹配其他记录的aid（主键）结果是查父节点

         --      如顺序颠倒，则是：有本记录的aid（主键）匹配其他记录的parentid，结果是查子节点

          

--测试5：递归关键字出现的位置

--需求：根据地区名称查找其所在省份

select s.areaname from  s_coalarea_test s

  where s.arealevel=1  --对遍历结果进行过滤

start with       --start with 必须出现在where之后，但是可以是where and之间 入下例      

    s.areaname='大同'

connect by prior              

  s.parentid=s.aid

--测试5：继续谈递归关键字出现的位置

--需求：根据地区名称查找其所在省份

select s.areaname from  s_coalarea_test s

  where s.arealevel=1  --对遍历结果进行过滤

start with       --start with 之前不需要加and     

    s.areaname='大同'

connect by prior              

  s.parentid=s.aid

  and s.arealevel=1 -  --where的过滤条件

group by s.areaname  --既然递归查询也是过滤条件 当然可以跟group by

  order by s.areaname  --既然递归查询也是过滤条件 当然可以跟order by

--总结：start with ...connect by prior 递归查询也是对结果过滤，类似where过滤，先于where过滤执行