Oracle同行合并分組

 <b>Oracle</b><b>同行合并分組</b>

         使用函數sys_connect_by_path(column,'')的例子^^。

<b>表結構為：</b>

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;

<b>顯示結果為：</b>

BOO AUTHOR

-----------------

001 jack

001 tom

002 wang

002 zhang

002 li

<b>我們想得到的結果為：</b>

-----------------------------

001 jack&amp;&amp;tom

002 wang&amp;&amp;zhang&amp;&amp;li

<b>SQL</b><b>文為：</b>

select bookid,substr(max(sys_connect_by_path(author,'&amp;&amp;')),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;

<b>詳細解釋：</b>

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 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

繼續，把上述看成一張虛拟表，用到我們的sys_connect_by_path函數取出想要的值。

格式：

sys_connect_by_path(column,'')

start with 條件1

connect by 條件2(prior 子節點=父節點)

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

connect by prior id=pid;

-----------------------------------

001 &amp;&amp;jack

001 &amp;&amp;jack&amp;&amp;tom

002 &amp;&amp;wang

002 &amp;&amp;wang&amp;&amp;zhang

002 &amp;&amp;wang&amp;&amp;zhang&amp;&amp;li

OK，離我們的結果越來越近了，現在就是一般函數的應用了。

1，以bookid分組，取author的最大值。

2，用substr(string,start,length)截掉前面多餘的字元。//沒第三參數預設取到結束

......

group by bookid;//詳細sql文，一開始已給出!

------------------------------

大功告成，^_^!

drop table test;

      本文轉自winorlose2000 51CTO部落格，原文連結：http://blog.51cto.com/vaero/790594，如需轉載請自行聯系原作者