趣味SQL：微软面试题：过河

SGPM_OUT · 发表于 2012-3-27 09:53

hythuyateng 发表于 2012-3-27 09:26
look this:

select sum(times) total_time from (

楼上的AB一起过去了你没计算A回去的时间后面A怎么和C,D再过来！
还是理解错了请明示！

visual2006 · 发表于 2012-3-27 10:05

过桥次序如下：
1. AB    2
2. A       1
3. CD    10
4. B       2
5. AB    2
总计用时17分钟。
楚风的还没仔细学习，但10楼的存在问题。

〇〇 · 发表于 2012-3-27 10:19

应该让速度差不多的一起走，要不跑得快的太亏了

vv2011 · 发表于 2012-3-27 11:18

solomon_007 · 发表于 2012-3-27 14:18

SQL> set serveroutput on;
SQL>
SQL> CREATE TABLE bridge_crossing (
  2       name  VARCHAR2(10) PRIMARY KEY
  3       ,time  NUMBER
  4       );

Table created

SQL>
SQL> INSERT INTO bridge_crossing VALUES ('A', 1);

1 row inserted
SQL> INSERT INTO bridge_crossing VALUES ('B', 2);

1 row inserted
SQL> INSERT INTO bridge_crossing VALUES ('C', 5);

1 row inserted
SQL> INSERT INTO bridge_crossing VALUES ('D', 10);

1 row inserted
SQL> COMMIT;

Commit complete

SQL> select * from bridge_crossing;

NAME          TIME
---------- ----------
A                1
B                2
C                5
D                10

SQL>
SQL> with t1 as
  2 (select replace(ltrim(sys_connect_by_path(name, ','), ','), ',', '') rnd_1
  3    from bridge_crossing
  4    where level = 2
  5 connect by nocycle prior name <> name),
  6  t2 as
  7 (select replace(ltrim(sys_connect_by_path(rn, ','), ','), ',', '') str_pos
  8    from (select level rn from dual connect by level <= 3)
  9    where level = 2
10 connect by nocycle prior rn <> rn),
11  t3 as
12 (select rnd_1,
13          rnd_2,
14          substr(remain, substr(str_pos, 1, 1), 1) ||
15          substr(remain, substr(str_pos, 2, 1), 1) rnd_3,
16          substr(rnd_1, 1, 1) || substr(remain, substr(str_pos, 1, 1), 1) ||
17          substr(remain, substr(str_pos, 2, 1), 1) remain_4
18    from (select rnd_1,
19                substr(rnd_1, 2, 1) rnd_2,
20                replace('ABCD', substr(rnd_1, 1, 1), '') remain,
21                str_pos
22             from t1, t2)),
23  t4 as
24 (select rnd_1,
25          rnd_2,
26          rnd_3,
27          substr(remain_4, rn, 1) rnd_4,
28          substr(remain_4, rn, 1) ||
29          replace(replace(replace('ABCD', substr(remain_4, 1, 1), ''),
30                         substr(remain_4, 2, 1),
31                         ''),
32                substr(remain_4, 3, 1),
33                '') rnd_5
34    from t3, (select level rn from dual connect by level <= 3)),
35  t5 as
36 (select rnd_1,
37          rnd_2,
38          rnd_3,
39          rnd_4,
40          rnd_5,
41          greatest(decode(substr(rnd_1, 1, 1),
42                         'A',
43                         1,
44                         'B',
45                         2,
46                         'C',
47                         5,
48                         'D',
49                         10),
50                   decode(substr(rnd_1, 2, 1), 'A', 1, 'B', 2, 'C', 5, 'D', 10)) +
51          decode(rnd_2, 'A', 1, 'B', 2, 'C', 5, 'D', 10) +
52          greatest(decode(substr(rnd_3, 1, 1),
53                         'A',
54                         1,
55                         'B',
56                         2,
57                         'C',
58                         5,
59                         'D',
60                         10),
61                   decode(substr(rnd_3, 2, 1), 'A', 1, 'B', 2, 'C', 5, 'D', 10)) +
62          decode(rnd_4, 'A', 1, 'B', 2, 'C', 5, 'D', 10) +
63          greatest(decode(substr(rnd_5, 1, 1),
64                         'A',
65                         1,
66                         'B',
67                         2,
68                         'C',
69                         5,
70                         'D',
71                         10),
72                   decode(substr(rnd_5, 2, 1), 'A', 1, 'B', 2, 'C', 5, 'D', 10)) sum_cost
73    from t4)
74  select *
75 from (select t5.*, rank() over(order by sum_cost) rk from t5)
76 where rk = 1
77  /

RND_1                                                                         RND_2 RND_3 RND_4 RND_5 SUM_COST       RK
-------------------------------------------------------------------------------- ----- ----- ----- ------ ---------- ----------
AB                                                                            B    CD A    AB          17       1
BA                                                                            A    DC B    BA          17       1
AB                                                                            B    DC A    AB          17       1
BA                                                                            A    CD B    BA          17       1

SQL>