Oracle同行合并分组

Oracle同行合并分组，使用函数sys_connect_by_path(column,

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