Home > Database > Mysql Tutorial > Oracle同行合并分组

Oracle同行合并分组

WBOY
Release: 2016-06-07 17:11:47
Original
1434 people have browsed it

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

linux

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template