with bus as
(select 'A' dstart, 'B' dend, '120' distance
from dual
union all
select 'B' dstart, 'C' dend, '200' distance
from dual
union all
select 'A' dstart, 'D' dend, '150' distance
from dual
union all
select 'D' dstart, 'M' dend, '300' distance
from dual
union all
select 'C' dstart, 'E' dend, '180' distance
from dual
union all
select 'F' dstart, 'M' dend, '260' distance from dual)
select level,
dstart,
dend,
connect_by_root dstart,
distance,
SYS_CONNECT_BY_PATH(dstart, '->') || '->' || dend,
/* DBMS_AW.eval_number(substr(sys_connect_by_path(distance, '*'),2)) AS sum_qty*/
substr(sys_connect_by_path(distance, '*'),2)
from bus
start with dstart is not null
connect by (prior dend = dstart);
-----------------------------------------------
with bus as
(
select 'A' dstart, 'B' dend, '120' distance from dual union all
select 'B' dstart, 'C' dend, '200' distance from dual union all
select 'A' dstart, 'D' dend, '150' distance from dual union all
select 'D' dstart, 'M' dend, '300' distance from dual union all
select 'C' dstart, 'E' dend, '180' distance from dual union all
select 'F' dstart, 'M' dend, '260' distance from dual
)
select * from bus;
------------------------------------------
with bus as
(
select 'A' dstart, 'B' dend, '120' distance from dual union all
select 'B' dstart, 'C' dend, '200' distance from dual union all
select 'A' dstart, 'D' dend, '150' distance from dual union all
select 'D' dstart, 'M' dend, '300' distance from dual union all
select 'C' dstart, 'E' dend, '180' distance from dual union all
select 'F' dstart, 'M' dend, '260' distance from dual
)
select dstart, wmsys.wm_concat(dend) from
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30018455/viewspace-1472161/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30018455/viewspace-1472161/