# ITPUB SQL大赛之BUG（八）

ITPUB SQL

ITPUB SQL

ITPUB SQL

ITPUB SQL

ITPUB SQL

ITPUB SQL

ITPUB SQL

SQL> WITH ROUTE_D AS /* get full routes */
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 /* get every route of any two cities */
10  (
11     SELECT R, T, CAST('"' || R || '"' || T || '"' AS VARCHAR2(4000)), DIS /* avoid ora-1489 error */
12     FROM ROUTE_D
13     UNION ALL
14     SELECT A.C1, T, LINES || T || '"', DIS + DISTANCE /* '"' for avoid city name contains other city name */
15     FROM ROUTE_D R, ROUTE_ALL_D A
16     WHERE A.C2 = R.R
17     AND INSTR(LINES, '"' || T || '"', 1, 1) = 0 /* avoit duplicate city */
18     AND DISTANCE + DIS <= /* filter the distance longer than routes */
19             NVL
20             (
21                     (
22                             SELECT DISTANCE
23                             FROM ROUTES RS
24                             WHERE (A.C1 = RS.CITY1
25                                     AND R.T = RS.CITY2)
26                             OR (A.C1 = RS.CITY2
27                                     AND R.T = RS.CITY1)
28                     ),
29                     9.9E38
30             )
31  ),
32  RESULT_HALF AS
33  (
34     SELECT C1 R, C2 T, MIN(DISTANCE) DIS
35     FROM ROUTE_ALL_D
36     WHERE C1 < C2
37     GROUP BY C1, C2
38  ),
39  RESULT_ALL AS
40  (
41     SELECT R, T, DIS
42     FROM RESULT_HALF
43     UNION ALL
44     SELECT T, R, DIS
45     FROM RESULT_HALF
46  ),
47  RESULT AS
48  (
49     SELECT R, T,
50             DISTANCE * 2 * C.MEMBERS COST,
51             SUM(DISTANCE * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
52     FROM RESULT_ALL R, CITIES C
53     WHERE R.T = C.CITY_NAME(+) /* any city in route can win even the city have no member */
54  )
55  SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
56  FROM
57  (
58     SELECT R, T, COST, RANK() OVER(ORDER BY COST_CITY) RN
59     FROM RESULT
60  )
61  WHERE RN = 1
62  GROUP BY GROUPING SETS ((R, T), R)
63  ORDER BY R, DECODE(T, 'TOTAL', CHR(0), T);
SUM(DISTANCE * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
*
51 行出现错误:
ORA-06553: PLS-306:

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
2  FROM DBA_OBJECTS
3  WHERE OBJECT_NAME = 'DISTANCE';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
PUBLIC     DISTANCE                       SYNONYM

SQL> SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
2  FROM DBA_SYNONYMS
3  WHERE SYNONYM_NAME = 'DISTANCE';

OWNER      SYNONYM_NAME    TABLE_OWNER     TABLE_NAME
---------- --------------- --------------- ------------------------------
PUBLIC     DISTANCE        MDSYS           OGC_DISTANCE

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
2  FROM DBA_OBJECTS
3  WHERE OBJECT_NAME = 'OGC_DISTANCE'
4  AND WNER = 'MDSYS';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
MDSYS      OGC_DISTANCE                   FUNCTION

• 博文量
1954
• 访问量
10634718