/*
*运行环境oracle 11g
*1、将所有的每两个城市的路线作为临时表
*2、通过oracle with递归求出所有的路径
*3、筛选出从上一步中求出的所有城市举办时，最短的路径
*4、求出最小费用举办的城市
*5、格式化输出
*/
WITH ALLROUTES--所有的路径，包括往返的
AS
(
SELECT  CITY1,
         CITY2,
         '/'||CITY1 AS PATH,
         DISTANCE
       FROM ROUTES
UNION ALL
SELECT  CITY2 CITY1,
        CITY1 CITY2,
        '/'||CITY1 AS PATH,
        DISTANCE
FROM ROUTES
),
ROUTES_LENGTH(CITY1,CITY2,PATH,DISTANCE)--所有可以选择的路径
AS(
       SELECT  CITY1,
               CITY2,
               '/'||CITY1 AS PATH,
               DISTANCE
       FROM ALLROUTES
       UNION ALL
       SELECT  AR.CITY1,
               AR.CITY2,
               RL.PATH||'/'||AR.CITY1,
               RL.DISTANCE+AR.DISTANCE
       FROM ROUTES_LENGTH RL,ALLROUTES AR
       WHERE RL.CITY2=AR.CITY1
       AND INSTR(RL.PATH,'/'||AR.CITY2)=0
),
MIN_ROUTES_LENGTH--筛选出在每个城市举办的最短路径
AS
(
SELECT REGEXP_SUBSTR(RL.PATH||'/'||RL.CITY2,'[^/]+') C1,
       REGEXP_SUBSTR(RL.PATH||'/'||RL.CITY2,'[^/]+',1,LENGTH(REGEXP_REPLACE(RL.PATH||'/'||RL.CITY2, '[^/]+',''))) C2,
       MIN(DISTANCE) MC
FROM ROUTES_LENGTH RL
GROUP BY REGEXP_SUBSTR(RL.PATH||'/'||RL.CITY2,'[^/]+'),
         REGEXP_SUBSTR(RL.PATH||'/'||RL.CITY2,'[^/]+',1,LENGTH(REGEXP_REPLACE(RL.PATH||'/'||RL.CITY2, '[^/]+','')))
),
MIN_ROUTES_MONEY--求出每个城市最短路径所需要的费用
AS
(
SELECT MR.C1,MR.C2,MR.MC*C.MEMBERS*2 EVERY_VAL,SUM(MR.MC*C.MEMBERS*2) OVER(PARTITION BY MR.C1) ALL_VAL
FROM MIN_ROUTES_LENGTH MR,CITIES C
WHERE MR.C1=C.CITY_NAME

这里应该是mr.c2啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊，一点写错，全部错了，怪自己测试不仔细啊！！

---

),
MIN_ROUTES_MONEY_CITY--求出最小费用举办的城市
AS
(
SELECT MRM.C1,MRM.C2,EVERY_VAL,ALL_VAL,RANK()OVER(ORDER BY ALL_VAL) LVL
FROM MIN_ROUTES_MONEY MRM
)
SELECT MRC.C1,
       CASE GROUPING(MRC.C2) WHEN 1 THEN 'TOTAL'
            ELSE MRC.C2 END c2,
       SUM(MRC.EVERY_VAL)
FROM MIN_ROUTES_MONEY_CITY MRC
WHERE LVL=1
GROUP BY GROUPING SETS((MRC.C1,MRC.C2,MRC.EVERY_VAL),(MRC.C1))
ORDER BY MRC.C1,decode(c2,'TOTAL','0',c2);