转载地址:http://blog.sina.com.cn/s/blog_497b6d490100rc3r.html
利用Oracle分析函数实现多行数据合并为一行
select ename,deptno from emp order by deptno;
<wbr><wbr><wbr></wbr></wbr></wbr>
ENAME | DEPTNO |
CLARK | 10 |
KING | 10 |
MILLER | 10 |
SMITH | 20 |
ADAMS | 20 |
FORD | 20 |
SCOTT | 20 |
JONES | 20 |
ALLEN | 30 |
BLAKE | 30 |
MARTIN | 30 |
JAMES | 30 |
TURNER | 30 |
WARD | 30 |
<wbr><wbr><wbr>现在想要将同一部门的人给合并成一行记录,如何做呢?如下:</wbr></wbr></wbr>
<wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
ENAME | DEPTNO |
CLARK,KING,MILLER | 10 |
ADAMS,FORD,JONES,SCOTT,SMITH | 20 |
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 |
通常我们都是自己写函数或在程序中处理,这里我们利用oracle自带的分析函数row_number()和sys_connect_by_path来进行sql语句层面的多行到单行的合并,并且效率会非常高。
基本思路:
1、对deptno进行row_number()按ename排位并打上排位号
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename;
DEPTNO | ENAME | RANK |
10 | CLARK | 1 |
10 | KING | 2 |
10 | MILLER | 3 |
20 | ADAMS | 1 |
20 | FORD | 2 |
20 | JONES | 3 |
20 | SCOTT | 4 |
20 | SMITH | 5 |
30 | ALLEN | 1 |
30 | BLAKE | 2 |
30 | JAMES | 3 |
30 | MARTIN | 4 |
30 | TURNER | 5 |
30 | WARD | 6 |
可看出,经过row_number()后,部门人已经按部门和人名进行了排序,并打上了一个位置字段rank
2、利用oracle的递归查询connect by进行表内递归,并通过sys_connect_by_path进行父子数据追溯串的构造,这里要针对ename字段进行构造,使之合并在一个字段内(数据很多,只截取部分)
select deptno,ename,rank,level as curr_level,
ltrim(sys_connect_by_path(ename,','),',') ename_path from (
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank;
各部门递归后的数据量都是:(1+n)/2 * n 即:deptno=10 数据量:(1+3)/2 * 3 = 6;
deptno=20 数据量:(1+5)/2 * 5 = 15;<wbr><wbr><wbr><wbr><wbr>deptno=30 数据量:(1+6)/2 * 6 = 21;</wbr></wbr></wbr></wbr></wbr>
DEPTNO | ENAME | RANK | CURR_LEVEL | ENAME_PATH |
10 | CLARK | 1 | 1 | CLARK |
10 | KING | 2 | 2 | CLARK,KING |
10 | MILLER | 3 | 3 | CLARK,KING,MILLER |
10 | KING | 2 | 1 | KING |
10 | MILLER | 3 | 2 | KING,MILLER |
10 | MILLER | 3 | 1 | MILLER |
<wbr></wbr>
DEPTNO | ENAME | RANK | CURR_LEVEL | ENAME_PATH |
20 | ADAMS | 1 | 1 | ADAMS |
20 | FORD | 2 | 2 | ADAMS,FORD |
20 | JONES | 3 | 3 | ADAMS,FORD,JONES |
20 | SCOTT | 4 | 4 | ADAMS,FORD,JONES,SCOTT |
20 | SMITH | 5 | 5 | ADAMS,FORD,JONES,SCOTT,SMITH |
20 | FORD | 2 | 1 | FORD |
20 | JONES | 3 | 2 | FORD,JONES |
20 | SCOTT | 4 | 3 | FORD,JONES,SCOTT |
20 | SMITH | 5 | 4 | FORD,JONES,SCOTT,SMITH |
20 | JONES | 3 | 1 | JONES |
20 | SCOTT | 4 | 2 | JONES,SCOTT |
20 | SMITH | 5 | 3 | JONES,SCOTT,SMITH |
20 | SCOTT | 4 | 1 | SCOTT |
20 | SMITH | 5 | 2 | SCOTT,SMITH |
20 | SMITH | 5 | 1 | SMITH |
这里我们仅列出deptno=10、20的,至此我们应该能否发现一些线索了,即每个部门中,curr_level最高的那行,有我们所需要的数据。那后面该怎么办,取出那个数据? 对了,继续用row_number()进行排位标记,然后再按排位标记取出即可。
3、 对deptno继续进行row_number()按curr_level排位
select deptno,ename_path,row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank from (select deptno,ename,rank,level as curr_level,
ltrim(sys_connect_by_path(ename,','),',') ename_path from (
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank);
DEPTNO | ENAME_PATH | ENAME_PATH_RANK |
10 | CLARK,KING,MILLER | 1 |
10 | CLARK,KING | 2 |
10 | KING,MILLER | 3 |
10 | CLARK | 4 |
10 | KING | 5 |
10 | MILLER | 6 |
DEPTNO | ENAME_PATH | ENAME_PATH_RANK |
20 | ADAMS,FORD,JONES,SCOTT,SMITH | 1 |
20 | ADAMS,FORD,JONES,SCOTT | 2 |
20 | FORD,JONES,SCOTT,SMITH | 3 |
20 | ADAMS,FORD,JONES | 4 |
20 | FORD,JONES,SCOTT | 5 |
20 | JONES,SCOTT,SMITH | 6 |
20 | ADAMS,FORD | 7 |
20 | FORD,JONES | 8 |
20 | SCOTT,SMITH | 9 |
20 | JONES,SCOTT | 10 |
20 | ADAMS | 11 |
20 | JONES | 12 |
20 | SMITH | 13 |
20 | SCOTT | 14 |
20 | FORD | 15 |
这里还是仅列出deptno为10、20的,至此应该很明了了,在进行一次查询,取ename_path_rank为1的即可获得我们想要的结果。
4、获取想要排位的数据,即得部门下所有人多行到单行的合并
select deptno,ename_path from (select deptno,ename_path,
row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank
from (select deptno,ename,rank,level as curr_level,
ltrim(sys_connect_by_path(ename,','),',') ename_path from (
select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank
from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank))
where ename_path_rank=1;
<wbr></wbr>
![ORACLE多行合并为一行。 ORACLE多行合并为一行。](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> select <wbr><wbr><wbr><wbr><wbr><wbr><wbr>empno,<wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>deptno,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ename,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>rank,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> level <wbr></wbr> as <wbr>curr_level,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> ltrim (sys_connect_by_path(ename,<wbr></wbr> ' , ' ),<wbr></wbr> ' , ' )<wbr>ename_path<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> from <wbr>(</wbr> select <wbr>deptno,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ename,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>empno,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>row_number()<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> over (partition<wbr></wbr> by <wbr>deptno<wbr></wbr></wbr> order <wbr></wbr> by <wbr>deptno,<wbr>ename)<wbr>rank<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> from <wbr>emp<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> order <wbr></wbr> by <wbr>deptno,<wbr>ename)<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>connect<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> by <wbr>deptno<wbr></wbr></wbr> = <wbr>prior<wbr>deptno<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> and <wbr>rank<wbr></wbr></wbr> - <wbr></wbr> 1 <wbr></wbr> = <wbr>prior<wbr>rank<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>))<wbr> </wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> where <wbr>ename_path_rank<wbr></wbr></wbr> = <wbr></wbr> 1 ;
—————————————————————————————————————————————————
例:<wbr><wbr>id<wbr>name<br><wbr><wbr><wbr><wbr><wbr><wbr>1<wbr><wbr>aa<br><wbr><wbr><wbr><wbr><wbr><wbr>2<wbr><wbr>bb<br><wbr><wbr><wbr><wbr><wbr><wbr>3<wbr><wbr>cc<br><br><wbr>要的结果是"aa,bb,cc"</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
select WMSYS.WM_CONCAT(a.name) from user a
这样的话,查询出的结果:"aa.bb.cc"
中间用点间隔,如果想替换为其他符号,例如用逗分号
select replace(WMSYS.WM_CONCAT(a.name),',',';') from user a
结果:"aa;bb;cc"