# 四人过桥问题的SQL解法

A B,A,A C,A,A D  19

SQL> WITH C AS
2  (SELECT NAME, TIME, POWER(2, ROWNUM - 1) POS FROM BRIDGE_CROSSING),
3  A AS
4  (SELECT 0 RN, A.NAME || ' ' || B.NAME NAME, GREATEST(A.TIME, B.TIME) TIME, A.POS + B.POS POS
5  FROM C A, C B
6  WHERE A.NAME < B.NAME
7  UNION ALL
8  SELECT 1, NAME, TIME, POS FROM C),
9  B (RN, NAME, TIME, POS) AS
10  (SELECT 0 RN, CAST ('' AS VARCHAR2(4000)) NAME, 0, 0 POS FROM DUAL
11  UNION ALL
12  SELECT B.RN + 1,
13     B.NAME || ',' || A.NAME,
14     B.TIME + A.TIME,
15     DECODE(MOD(B.RN, 2), 0, B.POS + A.POS, B.POS - A.POS)
16  FROM B, A
17  WHERE MOD(B.RN, 2) = A.RN
18  AND DECODE(MOD(B.RN, 2), 0, BITAND(B.POS, A.POS), BITAND(B.POS - A.POS, A.POS)) = 0)
19  SELECT LTRIM(NAME, ',') NAME, TIME FROM
20  (SELECT NAME, TIME, RANK() OVER(ORDER BY TIME) RN
21  FROM B
22  WHERE POS = POWER(2, 4) - 1)
23  WHERE RN = 1;

NAME                                 TIME
------------------------------ ----------
A B,A,C D,B,A B                        17
A B,B,C D,A,A B                        17

• 博文量
1955
• 访问量
10487467