作者：shenguokun
http://sinobestdba.itpub.net

一、问题：
表a
列 a1 a2
记录
1 a
1 b
2 x
2 y
2 z
用select能选成以下结果吗？
1 ab
2 xyz

二、飞行里程
表flying
列s_city,e_city,distance
create table flying (s_city varchar2(40),e_city varchar2(40),distance number);
insert into flying values ('guangzhou','beijing',3000);
insert into flying values ('beijing','xian',1800);
insert into flying values ('xian','shanghai',2200);
insert into flying values ('xian','nanning',2900);
insert into flying values ('nanning','kunming',1200);
insert into flying values ('shanghai','kunming',3200);
insert into flying values ('shenzhen','luoding',500);

从广州出发可能到达的城市及里程?

问题一解答:

create table a (a1 varchar2(20),a2 varchar2(20));
insert into a values ('1','a');
insert into a values ('1','b');
insert into a values ('2','x');
insert into a values ('2','y');
insert into a values ('2','z');
insert into a values ('3','!');
insert into a values ('3','@');
insert into a values ('3','#');
insert into a values ('3','$');
commit;

SELECT distinct a1,first_value(a3) over (partition by a1 order BY l DESC)
from (
SELECT a1,a2,LEVEL l,replace(sys_connect_by_path(a2,';'),';') a3
FROM (SELECT a1||ROWNUM c,a1||ROWNUM-1 p,a1,a2 FROM a)
CONNECT BY PRIOR c = p )

问题二解答:

select level l,e_city,'guangzhou'||sys_connect_by_path(e_city,'/') hx from flying start with s_city='guangzhou' connect by s_city=prior e_city order by l;