sql递归查询在sqlserver中正常,在oracle中貌似死循环,大神帮忙啊!

_笔记本_ 发布于 2016/05/13 22:14
阅读 220
收藏 0

下面的sql递归查询在sqlserver中正常,在oracle中貌似死循环,大神帮忙啊!
tb_pathinfo表中有pathinfo字段,该字段类型为字符串,每7个长度代表一个节点编码
例如:pathinfo=aaaaaaabbbbbbbcccccccddddddd 下面的sql可获取结果为:

sta1            sta2
----------------------
aaaaaaa    bbbbbbb
bbbbbbb    ccccccc
ccccccc    ddddddd

with t(sta1, sta2, pathinfo) as (
    select cast('0000000' as char(7)) as sta1, 
      cast('0000000' as char(7)) as sta2, pathinfo 
    from tb_pathinfo 
        
union all
        
    select cast(substring(t.pathinfo, 1, 7) as char(7)) as sta1, 
      cast(substring(t.pathinfo, 8, 7) as char(7)) as sta2, 
      substring(t.pathinfo, 8, len(t.pathinfo) - 7) as pathinfo
    from t
    where len(t.pathinfo)>7
) 
select sta1, sta2 from t 
where sta2 <> '0000000'




加载中
0
jolphin
jolphin

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 亲测可用

with tb_pathinfo as (
select 'aaaaaaabbbbbbbcccccccddddddd' pathinfo from dual
), t(sta1, sta2, pathinfo) as (
select cast('0000000' as char(7)) as sta1, 
       cast('0000000' as char(7)) as sta2, 
			 pathinfo 
from tb_pathinfo 
union all
select cast(substr(t.pathinfo, 1, 7) as char(7)) as sta1, 
       cast(substr(t.pathinfo, 8, 7) as char(7)) as sta2, 
       substr(t.pathinfo, 8, length(t.pathinfo) - 7) as pathinfo
from t
where length(t.pathinfo) > 7) 
select sta1, sta2 from t 
where sta2 <> '0000000'



_笔记本_
_笔记本_
回复 @jolphin : 再次感谢,经测试完全可用,包括多行
jolphin
jolphin
回复 @_笔记本_ : 多行会有问题, 需要一个唯一标示
_笔记本_
_笔记本_
感谢您的回复,但是我单看sql的话没看出区别,只是tb_pathinfo只有一行数据,如果多行不知结果是否正确,我下午去试试,感谢
返回顶部
顶部