典型sql求解

最新推荐文章于 2025-06-15 16:40:38 发布

转载最新推荐文章于 2025-06-15 16:40:38 发布 · 130 阅读

本文通过创建示例表并插入数据，展示了如何使用SQL的CONNECT BY和SYS_CONNECT_BY_PATH函数来实现层级查询和路径拼接，包括如何按特定顺序连接字符串以及构建从起点到各个节点的路径。

作者：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);

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

[@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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25152497/viewspace-1044885/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25152497/viewspace-1044885/