--测试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 --测试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
递归变形:
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; 那查询到的结果如下,有很多重复信息的 爸爸,孙子SB 儿子,孙子SB 孙子NB,孙子SB 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最后面再加. CONNECT_BY_ISCYCLE 我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是,一旦数据中出现了循环记录,如两个节点互为对方父节点,系统就会报ORA-01436错误: insert into t_tonedirlib(dirindex,fatherindex,dirname,status) values (666,667,'123',5); 1 row inserted insert into t_tonedirlib(dirindex,status) values (667,666,'456',5); 1 row inserted select dirindex,RPAD(' ',2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 666 connect by fatherindex = prior dirindex ORA-01436: 用户数据中的 CONNECT BY 循环 10G中,可以通过加上NOCYCLE关键字避免报错。并且通过CONNECT_BY_ISCYCLE属性就知道哪些节点产生了循环: select CONNECT_BY_ISCYCLE,dirindex,2*(LEVEL-1)) || dirname from t_tonedirlib start with fatherindex = 666 connect by NOCYCLE fatherindex = prior dirindex CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname ----------------- ---------------- --------------------------------- 0 667 666 456 1 666 667 123 2 rows selected 以上就是在10G中增强的CONNECT BY了。当然对于这些增强特性的作用肯定不止如上介绍的,还需要更多高人去挖掘了。
递归查询例子:
例子: select s.id,s.PID,s.NAME,s.CATLEVEL from T_CATALOG s where s.catlevel=3 --对遍历结果进行过滤 start with --start with 必须出现在where之后,但是可以是where and之间 入下例 s.id=27701 connect by prior s.id=s.pid 关联商品 SELECT * FROM ( SELECT s.id,s.CATLEVEL from T_CATALOG s where s.catlevel=3 --对遍历结果进行过滤 start with --start with 必须出现在where之后,但是可以是where and之间 入下例 s.id=27701 connect by prior s.id=s.pid) cat JOIN T_PRODUCT p ON p.CATALOGID=cat.ID 如果直接join,需要用left join SELECT p.NAME,s.id,s.CATLEVEL from T_CATALOG s left JOIN T_PRODUCT p ON p.CATALOGID=s.ID where s.catlevel=3 AND p.NAME IS NOT null --对遍历结果进行过滤 start with --start with 必须出现在where之后,但是可以是where and之间 入下例 s.id=27701 connect by prior s.id=s.pid 外面join查数量: SELECT cat.id,cat.name,count(p.ID) count FROM ( SELECT s.id,s.CATLEVEL from T_CATALOG s where s.catlevel=3 --对遍历结果进行过滤 start with --start with 必须出现在where之后,但是可以是where and之间 入下例 s.id=27701 connect by prior s.id=s.pid) cat JOIN T_PRODUCT p ON p.CATALOGID=cat.ID GROUP by cat.id,cat.name
查询父集合:
--测试4:谈级联条件 --需求:根据地区名,查询上级地区 select s.areaname from s_coalarea_test s start with s.areaname='大同' connect by prior s.parentid=s.aid --注意,此时仅仅是级联条件顺序改变了 --总结:到底是查父节点还是子节点,有级联顺序决定 --规律:【本记录字段】=【连接字段】 --如本实例:是通过本记录的parentid匹配其他记录的aid(主键)结果是查父节点 -- 如顺序颠倒,则是:有本记录的aid(主键)匹配其他记录的parentid,结果是查子节点 select s.id,s.CATLEVEL from T_CATALOG s where s.catlevel=1 --对遍历结果进行过滤 start with --start with 必须出现在where之后,但是可以是where and之间 入下例 s.id=27703 connect by prior s.pid=s.id (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|