SQL基础8.2——层次化查询(START BY ... CONNECT BY PRIOR)
来源:互联网 发布:iphone解压缩软件 知乎 编辑:程序博客网 时间:2024/06/14 03:16
今天在ITPUB看到的题目
2、有如下信息:
起始地 目的地 距离(公里)
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出发,可达到的目的地(包括间接方式)。
创建表test
SQL> select * from test;
START_PLACE END_PLACE DISTANCE
----------- ---------- ----------
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
select start_place, end_place
from (select * from testwhere end_place <> 'A')
start with start_place = 'A'
connect by prior end_place = start_place;
START_PLACE END_PLACE
----------- ----------
A C
C B
B D
D F
F G
A D
D F
F G
A E
这些条目中的END_PLACE就是所有可以从A到达的点
connect by prior 实现树状查询 connect by prior 左边的为父接点,右边为自接点。
oracle还提供函数sys_connect_by_path()可以把路径表示出来如下面语句
select sys_connect_by_path(start_place, '->')||'->'||end_place
from (select * from duanjw where end_place <> 'A')
start with start_place = 'A'
CONNECT BY PRIOR end_place = start_place;
SYS_CONNECT_BY_PATH(START_PLAC
--------------------------------------------------------------------------------
->A->C
->A->C->B
->A->C->B->D
->A->C->B->D->F
->A->C->B->D->F->G
->A->D
->A->D->F
->A->D->F->G
->A->E
这样看起来就清楚了很多
- SQL基础8.2——层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- SQL 基础8.1——层次化查询(START BY ... CONNECT BY PRIOR)
- sql 层次化查询(START BY ... CONNECT BY PRIOR)
- SQL——层次化查询(START BY ... CONNECT BY PRIOR)
- 数据库学习笔记---SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
- 层次化查询(START BY ... CONNECT BY PRIOR)
- 层次化查询(START BY ... CONNECT BY PRIOR)
- 层次化查询start with...connect by prior... -Oracle
- 层次查询START WITH... CONNECT BY PRIOR、LEVEL伪列
- 递归查询START WITH CONNECT BY PRIOR
- codeforces 134Cswap
- 操作系统的判断
- spring基础学习二(配置文件)
- 跟我学android应用开发 之 第一个Android应用程序
- Microsoft Visual C++ 2005 Redistributable不能删除旧版本的问题
- SQL基础8.2——层次化查询(START BY ... CONNECT BY PRIOR)
- 英语单词词根词缀和词性转换
- C#垃圾回收
- 杜拉拉升职记1-学习笔记
- 杜拉拉升职记3-学习笔记
- verilog中的initial语句
- 如何在项目中用一台机器进行简单性能测试
- PHPCMS中模板中$forminfos循环输出表单的代码详解
- DSOFramer控件文档上传到服务器处理页面后,怎么解析数据 <转>