source depend depend_filename
IM200908210099 PM200908260001 rootcause
IM200908210099 PM200908260002 cm3t
IM200908210099 PM200908260003 rootcause
IM200908210099 PM200908260004 cm3t表名为a首先有一个编号IM200906250048,根据这个编号查询select source,depend,depend_filename from a where source='IM200906250048',
然后再把查询出来的depend当作SOURCE继续查select source,depend,depend_filename from a where source='PM200908260001',
然后再将查询出来的depend再作为source继续查询,就这样一直循环下去,直到查询出来的depend没有数据为止,请问一下大家该怎么处理呢?
IM200908210099 PM200908260001 rootcause
IM200908210099 PM200908260002 cm3t
IM200908210099 PM200908260003 rootcause
IM200908210099 PM200908260004 cm3t表名为a首先有一个编号IM200906250048,根据这个编号查询select source,depend,depend_filename from a where source='IM200906250048',
然后再把查询出来的depend当作SOURCE继续查select source,depend,depend_filename from a where source='PM200908260001',
然后再将查询出来的depend再作为source继续查询,就这样一直循环下去,直到查询出来的depend没有数据为止,请问一下大家该怎么处理呢?
SELECT * from a
WHERE depend is not null
connect by prior depend=source start with source='IM200906250048'
select 'IM200906250048' source, 'PM200908260001' depend, 'rootcause' depend_filename from dual union all
select 'PM200908260001' source, 'IM200908210098' depend, 'cm3t' depend_filename from dual union all
select 'IM200908210098' source, 'PM200908260003' depend, 'rootcause' depend_filename from dual union all
select 'IM200908210099' source, 'IM200908210098' depend, 'cm3t' depend_filename from dual)
SELECT LEVEL, t.*, sys_connect_by_path(t.source, '->') path
FROM test t
START WITH SOURCE = 'IM200906250048'
CONNECT BY SOURCE = PRIOR depend;