首页 » ORACLE 9i-23ai » 一个面试题

一个面试题

有如下信息：
起始地 目的地 距离（公里）
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发，可达到的目的地（包括间接方式）。

构表语句
create table test(fr_ad char(2),
to_ad char(2),
len int);

insert into test values(‘A’,’B’,1000);
insert into test values(‘A’,’C’,1100);
insert into test values(‘A’,’D’,900 );
insert into test values(‘A’,’E’,400 );
insert into test values(‘B’,’D’,300 );
insert into test values(‘D’,’F’,600 );
insert into test values(‘E’,’A’,400 );
insert into test values(‘F’,’G’,1000);
insert into test values(‘C’,’B’,600 );

—
我个人的做法是
SQL> select fr_ad,lpad(‘>>’,2*(level-1),’>’)||to_ad step from test start with fr_ad=’A’ connect by nocycle prior to_ad=fr_ad;

FR STEP
— ——————————
A B
B >>D
D >>>>F
F >>>>>>G
A C
C >>B
B >>>>D
D >>>>>>F
F >>>>>>>>G
A D
D >>F
F >>>>G
A E
E >>A
A >>>>B
B >>>>>>D
D >>>>>>>>F
F >>>>>>>>>>G
A >>>>C
C >>>>>>B
B >>>>>>>>D
D >>>>>>>>>>F
F >>>>>>>>>>>>G
A >>>>D
D >>>>>>F
F >>>>>>>>G

26 rows selected.

这是10g的特性，如果不用特性怎么做呢？呵呵

知道其它答案的给我恢复一下

打赏

目前这篇文章有4条评论(Rss)评论关闭。

  1. Fredricka Degeorge | #1
    2011-12-21 at 05:39

    Thank you for the sensible critique. Me & my neighbor were just preparing to do a little research on this. We got a grab a book from our local library but I think I learned more from this post. I’m very glad to see such fantastic info being shared freely out there.

  2. admin | #2
    2011-04-19 at 12:37

    select sys_connect_by_path( fr_ad,’->’) || ‘->’ || to_ad as path,level
    from test
    start with fr_ad=’A’
    connect by nocycle prior to_ad=fr_ad and to_ad不等于’A’

    • Woods | #3
      2011-06-16 at 09:48

      Glad I’ve finally found stomheing I agree with!

      • Caiden | #4
        2011-11-11 at 17:54

        Shoot, who would have tohguht that it was that easy?

上一篇：

下一篇：