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

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

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