Oracle的listagg() 和row_number()的结合使用

1.查询员工表数据

select * from scott.emp

结果：

2.通过listadd()进行行转列

select t.deptno,listadd(t.ename,',') within group (order by t.sal desc) 
from scott.emp t group by t.deptno;

结果：

3.通过row_number()进行编号

select t.deptno,t.ename,t.sal,row_number() over(partition by t.deptno 
order by t.sal desc) rn from scott.emp t;

结果：

4.结合list add()和row_number()将各部门根据工资高低进行排名

select t1.deptno,listadd(t1.ename||'工资排第'||t1.rn||'位','、') within group 
(order by t1.rn) vm from (
select t.deptno,t.ename,t.sal,row_number() over(partition by t.deptno 
order by t.sal desc) rn from scott.emp t
)t1 group by t1.deptno

结果: