通过下边的例子展示了start with connect by 的用法。
start with expr 可以理解为,从expr处我要形成树,也就是起点;
connect by expr 表示expr成立时才放到树中作为一个节点,同样也连接了上层和下层。
create table treetest (col1 varchar2(4),col2 varchar2(4));
insert into treetest values ('1','你');
insert into treetest values ('1','好');
insert into treetest values ('1','哦');
insert into treetest values ('1','好');
insert into treetest values ('2','天');
insert into treetest values ('2','上');
insert into treetest values ('2','地');
insert into treetest values ('2','下');
insert into treetest values ('3','美');
insert into treetest values ('3','丽');
insert into treetest values ('3','一');
insert into treetest values ('3','天');
//查出行号,便于排序
select rownum num,t3.col1,t3.col2 from treetest t3;
//forConnectBy区分不同树,connect by 处用,forStartWith在同树中的顺序,start with 处用
select t1.col1 c1,to_number(t1.col1)+row_number() over (order by t1.num) forConnectBy,
row_number() over(partition by t1.col1 order by t1.col1) forStartWith,t1.col2 c3
from (select rownum num,t3.col1,t3.col2 from treetest t3) t1;
//sys_connect_by_path(expr,'分隔符'),start with 从这里开始找树,connect by 连接成一棵树的条件
select replace(max(sys_connect_by_path(t2.c3,'/')),'/','')
from (select t1.col1 c1,to_number(t1.col1)+row_number() over (order by t1.num) forConnectBy,
row_number() over(partition by t1.col1 order by t1.col1) forStartWith,t1.col2 c3
from (select rownum num,t3.col1,t3.col2 from treetest t3) t1) t2
start with t2.forStartWith = 1
connect by prior t2.forConnectBy = t2.forConnectBy - 1 group by t2.c1 order by t2.c1;
输出结果:你好哦好 天上地下 美丽一天
分享到:
相关推荐
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
Oracle start with.connect by prior子句实现递归查询
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
oracle中的数查询,介绍的详细,有例子。
本文章详细介绍了Oracle中connect by...start with...的用法。
oracle数据库startwith用法
sql语句中的经常用到start connect by,这里简单介绍一下它的使用
我是在连接模拟器时出现了这样的问题,由于我kill服务都不可以...adb connect 127.0.0.1:62001 4、如果还出现5037被占用,kill掉就可以了 adb kill-server adb start-server 来自于测试群:762141168 作者:遇上方知友
Oracle_start_with_connect_by_prior_用法[文].pdf
在Oracle中用Start with...Connect By子句递归查询
ORACLE查询树型关系(connect_by_prior_start_with)
《getting start with storm》中译电子书
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
"Reactive Programming with Angular and ngrx: Learn to Harness the Power of Reactive Programming with RxJS and ngrx Extensions" English | ISBN: 1484226194 | 2017 | 148 pages | PDF | 3 MB Manage your ...
Learning C++ by Creating Games With UE4 will start with the basics of C++: installing a code editor so you can begin to write C++ code. You will then learn how to write small, self-contained C++ ...
Start Small, Stay Small is a step-by-step guide to launching a self-funded startup. If you're a desktop, mobile or web developer, this book is your blueprint to getting your startup off the ground ...
用户启动冷启动 Usidistrib的ColdStart