掌握start with connect by 应用：按顺序列转行

编程技术  /  houtizong 发布于 3年前   94

通过下边的例子展示了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;

输出结果：你好哦好   天上地下  美丽一天