select root,nvl(city_name,TOTAL), sum(money) from (
select a.*,rank() over(order by sum_money) sum_money_rank from (
select b.city_name,a.root,b.members*a.sum_dis*2 money,sum(b.members*a.sum_dis*2) over(partition by a.root) sum_money from (
select root, city2,min(sum_dis) sum_dis from s where cycle_flag=N group by root, city2 --取得各城市间最小路径值
) a,cities b where a.city2=b.city_name
)a
) where sum_money_rank=1 --得到费用最小的举办城市
group by grouping sets(root,(root,city_name)) --分组显示
order by root,city_name nulls first --TOTAL放最前面
;
/*
Oracle10gR2，使用connect by语法,运行时间0.3秒
allroutes 根据routes得出包括反方向的所有路径组合,
CONNECT_BY_ROOT(city1) root表示出发城市,
sum_money_str表示路径距离的一个字符串，里面的内容如000+080+082+091这样的,
nocycle 用于终止循环路径,
CONNECT_BY_ROOT(city1) <> city2 优化用,当路径回到根节点时退出，
+use_merge(a,b) 优化用
与子查询select rownum rn from dual connect by rownum < (select count(*) from cities) 关联，用于计算sum_money_str的值
sum(substr(a.sum_money_str, (b.rn) * 4 + 1, 3)) distance用于计算sum_money_str值
grouping sets(city_name,(city_name,city2)这个子句用于分组统计总成本及各城市间成本。
*/
select root,nvl(city_name,TOTAL), sum(money)
from (select city_name, root, money, rank() over(order by sum_money) sum_money_rank
from (select b.city_name,a.root,b.members * a.distance * 2 money,sum(b.members * a.distance * 2) over(partition by a.root) sum_money
from (select root,city2,min(distance) distance
from (select /*+use_merge(a,b)*/ a.root,a.city2,rn1,sum(substr(a.sum_money_str, (b.rn) * 4 + 1, 3)) distance
&