Oracle peer grouping

Oracle peer grouping
Use the sys_connect_by_path (column, '') function as an example ^.

Table Structure: 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', 'lil'); commit; select * from test; the result is: boo author--001 jack001 tom002 wang002 zhang002 li: boo author---001 jack & tom002 wang & zhang & amp; 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

-- (The last column or is) lead (id) over (partition by bookid order by id desc) pid

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

Start with pid is nullconnect by prior id = pidgroup by bookid; Description: sys_connect_by_path (column, '') // column is the column name, ''the character to be added in the middle is not used to concatenate the result set (merge rows) for us, but to construct the tree path. Therefore, it must be used with connect. Test is only a common table. How can it be changed to a tree structure? We need to add a pid and id. We only need to add a rownum for the id. Select bookid, author, rownum id from test; boo author id--001 jack 1001 tom 2002 wang 3002 zhang 4002 li 5 and pid is not the parent node of the next record. Here we need the function lag () to retrieve the previous record, which is opposite to lead. // Change the lag (id) over (order by id) pid to 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 1001 tom 2 1002 wang 3 2002 zhang 4 3002 li 5 4 since we want to divide our pid by bookid, we need to add partition by in the analysis function over, after reading the following results, we can see what is different.

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 1001 tom 2 1002 wang 3002 zhang 4 3002 li 5 4

1 2 Next Page