表 TMP
字段
id name
001 a
001001 b
001001001 c
001002 d
001002001 e
. .
. .
. .需要显示结果为:
id name
001 a
001001 a/b
001001001 a/b/c
001002 d
001002001 d/e
. .
. .
. .其中id中都是以三位递增的 (001,001001)求教高手怎么用sql写出来啊 不胜感激!
字段
id name
001 a
001001 b
001001001 c
001002 d
001002001 e
. .
. .
. .需要显示结果为:
id name
001 a
001001 a/b
001001001 a/b/c
001002 d
001002001 d/e
. .
. .
. .其中id中都是以三位递增的 (001,001001)求教高手怎么用sql写出来啊 不胜感激!
decode(c.code, null, null, c.code || '/') ||
decode(b.code, null, null, b.code || '/') || a.code
from enp a, enp b, enp c
where substr(a.id, 1, length(a.id) - 3) = b.id(+)
and substr(a.id, 1, length(a.id) - 6) = c.id(+)
order by a.id
ltrim(SYS_CONNECT_BY_PATH(D.name, '/'), '/') CPATH,
rownum CATEG_ORDER
from (select id, name from test) D
start with D.id = '001'
connect by prior d.id = substr(d.id, 0, length(d.id) - 3)
order siblings by D.id
和楼上有同样的疑惑select id,substr(sys_connect_by_path(name,'/'),2)
from
( select id,name,substr(id,1,length(id)-3) pid from tmp )
start with pid is null
connect by prior id = pid
既然
001 a
001001 b
001001001 c 那为什么不能认为 001001001 = 001+001+001 = aaa
001001001 = 001+001001 =ab
001001001 = 001001+001 =ba
……而一定要人为 001001001 = c 呢?诸如此类等等我觉得LZ都没把规则说清楚呢