Oracle同行合并分组
使用函数sys_connect_by_path(column,'')的例子^^。
);
insert into test values('001','
tom');
insert into test values('002','wang');
insert into test values('002','zhang');
insert into test values('002','
li');
显示结果为:
-----------------
001 jack
001 tom
002 wang
002 zhang
002 li
我们想得到的结果为:
BOO AUTHOR
-----------------------------
001 jack&&tom;
002 wang&&zhang;&&li;
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
详细解释:
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