select ename,deptno from emp order by deptno;
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 |
现在想要将同一部门的人给合并成一行记录,如何做呢?如下:
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< |