Oracle同行合并分组
Oracle同行合并分组
Oracle同行合并分组
使用函数sys_connect_by_path(column,'')的例子^^。
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) pidfrom (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
|
评论暂时关闭