ITPUB SQL大赛第三期（二）

SQL大赛第三期解法的最终答案。

ITPUB SQL大赛第三期：http://yangtingkun.itpub.net/post/468/516164

SQL> WITH ROUTE_D AS
2  (
3   SELECT CITY1 R, CITY2 T, DISTANCE DIS
4   FROM ROUTES
5   UNION ALL
6   SELECT CITY2, CITY1, DISTANCE
7   FROM ROUTES
8  ),
9  ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS
10  (
11   SELECT R, T, CAST('"' || R || '"' || T || '"' AS VARCHAR2(4000)), DIS
12   FROM ROUTE_D
13   WHERE R != (SELECT MAX(GREATEST(CITY1, CITY2)) FROM ROUTES)
14   UNION ALL
15   SELECT A.C1, T, LINES || T || '"', DIS + DISTANCE
16   FROM ROUTE_D R, ROUTE_ALL_D A
17   WHERE A.C2 = R.R
18   AND INSTR(LINES, '"' || T || '"', 1, 1) = 0
19   AND DISTANCE + DIS <=
20    NVL
21    (
22     (
23      SELECT DISTANCE
24      FROM ROUTES RS
25      WHERE (A.C1 = RS.CITY1
26       AND R.T = RS.CITY2)
27      OR (A.C1 = RS.CITY2
28       AND R.T = RS.CITY1)
29     ),
30     9.9E38
31    )
32  ),
33  RESULT_HALF AS
34  (
35   SELECT C1 R, C2 T, MIN(DISTANCE) DIS
36   FROM ROUTE_ALL_D
37   WHERE C1 < C2
38   GROUP BY C1, C2
39  ),
40  RESULT_ALL AS
41  (
42   SELECT R, T, DIS
43   FROM RESULT_HALF
44   UNION ALL
45   SELECT T, R, DIS
46   FROM RESULT_HALF
47  ),
48  RESULT AS
49  (
50   SELECT R, T,
51    DIS * 2 * C.MEMBERS COST,
52    SUM(DIS * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
53   FROM RESULT_ALL R, CITIES C
54   WHERE R.T = C.CITY_NAME(+)
55  )
56  SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
57  FROM
58  (
59   SELECT R, T, COST, RANK() OVER(ORDER BY COST_CITY) RN
60   FROM RESULT
61  )
62  WHERE RN = 1
63  GROUP BY GROUPING SETS ((R, T), R)
64  ORDER BY R, DECODE(T, 'TOTAL', CHR(0), T)
65  ;

R          T                COST
---------- ---------- ----------
D          TOTAL           68356
D          A                3200
D          B                3224
D          C                3634
D          E                7300
D          F                7598
D          G                3840
D          H               14580
D          I                4400
D          J               12352
D          K                8228
D          L                   0

SQL大赛中提交的代码的唯一区别是第13行，这里是“WHERE R != (SELECT MAX(GREATEST(CITY1, CITY2)) FROM ROUTES)”，而SQL大赛中出现问题的语句是“WHERE R != (SELECT MAX(CITY_NAME) FROM CITIES)”。

• 博文量
1954
• 访问量
10691214