典型sql求解

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:94

本帖最后由 ioriakyo 于 2014-8-11 11:11 编辑

作者:

一、问题:
表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);
从广州出发可能到达的城市及里程?
[@more@]问题一解答:
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;

本文标签:
本文标题:典型sql求解
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】