Oracle同行合并分组

         使用函数sys_connect_by_path(column,'')的例子^^。
 
表结构为：
create table test(
bookid char(3) not null,
author varchar2(10) not null
);
 
insert into test values('001','jack');
insert into test values('001','tom');
insert into test values('002','wang');
insert into test values('002','zhang');
insert into test values('002','li');
 
commit;
 
select * from test;
显示结果为：
BOO AUTHOR
-----------------
001 jack
001 tom
002 wang
002 zhang
002 li
 
我们想得到的结果为：
BOO AUTHOR
-----------------------------
001 jack&&tom
002 wang&&zhang&&li
 
SQL
文为：

select bookid,substr(max(sys_connect_by_path(author,'&&')),3) author

from

(select bookid,author,id,lag(id) over(partition by bookid order by id) pid

--(最后一列或者为)lead(id) over(partition by bookid order by id desc) pid

from (select bookid,author,rownum id from test))

start with pid is null
connect by prior id=pid
group by bookid;
 
详细解释：
sys_connect_by_path(column,'')//column为列名，''中间加要添加的字符
这个函数本身不是用来给我们做结果集连接的（合并行），而是用来构造树路径的，所以需要和connect by一起使用。
 
test只是张普通表，怎样才能变成树结构呢？我们需要加一个pid和id。
 
id我们只需加一个rownum就好。
select bookid,author,rownum id from test;
BOO AUTHOR           ID
----------------------------
001 jack             1
001 tom              2
002 wang             3
002 zhang            4
002 li               5
 
而pid上一条记录不就是下一条记录的父节点了。这里我们需要函数lag()取前记录，和lead()相对。
//把lag(id) over(order by id) pid改成lead(id) over(order by id desc) pid效果一样

select bookid,author,id,lag(id) over(order by id) pid

from (select bookid,author,rownum id from test);

BOO AUTHOR           ID              PID
-------------------------------------------
001 jack             1
001 tom              2                1
002 wang             3                2
002 zhang            4                3
002 li               5                4
 
由于要按bookid分我们的pid，在分析函数over中我们需要加上partition by，一看下面结果我们就知道有什么不同了。

select bookid,author,id,lag(id) over(partition by bookid order by id) pid

from (select bookid,author,rownum id from test);

BOO AUTHOR           ID              PID
-------------------------------------------
001 jack             1
001 tom              2                1
002 wang             3
002 zhang            4                3
002 li               5                4
 
继续，把上述看成一张虚拟表，用到我们的sys_connect_by_path函数取出想要的值。
格式：
sys_connect_by_path(column,'')
start with 条件1
connect by 条件2(prior 子节点=父节点)
 

select bookid,sys_connect_by_path(author,'&&') author

from

(select bookid,author,id,lag(id) over(partition by bookid order by id) pid

from (select bookid,author,rownum id from test))

start with pid is null
connect by prior id=pid;
BOO AUTHOR
-----------------------------------
001 &&jack
001 &&jack&&tom
002 &&wang
002 &&wang&&zhang

002 &&wang&&zhang&&li

 
OK，离我们的结果越来越近了，现在就是一般函数的应用了。
1，以bookid分组，取author的最大值。
2，用substr(string,start,length)截掉前面多余的字符。//没第三参数默认取到结束

select bookid,substr(max(sys_connect_by_path(author,'&&')),3) author

......
group by bookid;//详细sql文，一开始已给出!
BOO AUTHOR
------------------------------
001 jack&&tom
 
002 wang&&zhang&&li
 
大功告成，^_^!
drop table test;