oracle A点出发到各地的路径及其距离

来源：互联网发布：mysql countdistinct 编辑：程序博客网时间：2024/05/16 15:56

求所有的可能路径及距离,样例数据如下：

with bus as(select 1 id, 'A' dstart, 'B' dend, '120' distance from dual union allselect 2 id, 'B' dstart, 'C' dend, '200' distance from dual union allselect 3 id, 'C' dstart, 'E' dend, '180' distance from dual union allselect 4 id, 'A' dstart, 'D' dend, '150' distance from dual union allselect 5 id, 'D' dstart, 'M' dend, '300' distance from dual union allselect 6 id, 'F' dstart, 'M' dend, '260' distance from dual)select * From bus;

实现效果:

SUM_DISTANCEPATH120A->B320A->B->C500A->B->C->E150A->D450A->D->M200B->C380B->C->E180C->E300D->M260F->M

原始SQL代码：

with bus as(select 1 id, 'A' dstart, 'B' dend, '120' distance from dual union allselect 2 id, 'B' dstart, 'C' dend, '200' distance from dual union allselect 3 id, 'C' dstart, 'E' dend, '180' distance from dual union allselect 4 id, 'A' dstart, 'D' dend, '150' distance from dual union allselect 5 id, 'D' dstart, 'M' dend, '300' distance from dual union allselect 6 id, 'F' dstart, 'M' dend, '260' distance from dual )select level, a.dstart, a.dend,        sys_connect_by_path(distance,'+')  sum_distance,       (select sum(b.distance) from bus b start with b.id=a.id connect by prior dstart =  dend       ),       CONNECT_BY_root(a.dstart)|| sys_connect_by_path(a.dend,'->')  path,       CONNECT_BY_ISLEAF leaf      from bus aconnect by prior dend =  dstart;

一看这个SQL语句里有一个标量子查询，数据量少的时候看不出来问题，多的时候就会出现性能问题，因此利用dbms_aw.eval_number函数处理了树形函数的返回值,优化后的SQL代码：

with bus as(select 1 id, 'A' dstart, 'B' dend, '120' distance from dual union allselect 2 id, 'B' dstart, 'C' dend, '200' distance from dual union allselect 3 id, 'C' dstart, 'E' dend, '180' distance from dual union allselect 4 id, 'A' dstart, 'D' dend, '150' distance from dual union allselect 5 id, 'D' dstart, 'M' dend, '300' distance from dual union allselect 6 id, 'F' dstart, 'M' dend, '260' distance from dual )select level, a.dstart, a.dend, ltrim(sys_connect_by_path(distance,'+'),'+') distance_expression,       dbms_aw.eval_number(ltrim(sys_connect_by_path(distance,'+'),'+'))  sum_distance,       CONNECT_BY_root(a.dstart)|| sys_connect_by_path(a.dend,'->')  path,       CONNECT_BY_ISLEAF leaf      from bus aconnect by prior dend =  dstart;

参考:http://blog.csdn.net/jgmydsai/article/details/38985577

0 0