listagg函数的用法_db2行转列函数

两道SQL面试题引出listagg函数：

1. 用一条sql求出每个部门（emp表）的最大工资和最小工资，以及最大工资和最小工资的员工姓名。

（注：一次表扫描。同一个部门最大工资或最小工资的人可能不止一个）。

2. 需求：有时为了方便打印，会要求多行多列打印，如打印emp.ename列，类似下面这样显示：

ALLEN JONES MARTIN SMITH WARD

BLAKE CLARK KING SCOTT TURNER

ADAMS FORD JAMES MILLER

listagg函数是oracle11.2以后推出的一个新函数，使用该函数实现了行转列的功能，该数据与wmsys.wm_concat函数功能类似。
简单的说就是在分组合并后，把某列数据逐个枚举出来，其实也是一个行转列的效果。

如下,原始数据：

实现效果：

sql语句举例说明：

select nation, listagg(city,’,’) within group (order by city) as city

from test

group by nation

1：使用该函数必须的进行分组(group by 或使用分析函数进行分组)
2：listagg函数第一个参数表示需要进行枚举的字段，第二个参数表示枚举数据的分隔符
3：对于枚举的字段同时还需要排序和分组within group(order by xx)

利用网络上的例子：

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  

select nation,listagg(city,',') within GROUP (order by city)  city
from temp  
group by nation;

–利用wmsys.wm_concat实现相似的效果

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,wmsys.wm_concat(city) 
from temp  
group by nation;

wmsys.wm_concat函数默认枚举的数据是’,’分隔开的，而listagg可以自定义分隔符

–利用over(partition by XXX) 分析函数实现分组产生以上效果

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)

select nation,    
       listagg(city,',') within GROUP (order by city) over (partition by nation) city  
from temp;

listagg函数作为分析函数的一部分存在。

理解完listagg函数上面的面试题也就容易多了，如下：

1. 用一条sql求出每个部门（emp表）的最大工资和最小工资，以及最大工资和最小工资的员工姓名。
（注：一次表扫描。同一个部门最大工资或最小工资的人可能不止一个）。

   select  deptno,
         max(sal) max_sal,
         listagg(decode(rn1, 1, ename, null), ',') within group(order by ename) max_sal_ename,
         min(sal) min_sal,
        listagg(decode(rn2, 1, ename, null), ',') within group(order by ename) min_sal_ename
 from
 (select deptno,
         ename,
         sal,
        dense_rank() over(partition by deptno order by sal desc) rn1,
        dense_rank() over(partition by deptno order by sal) rn2
   from emp)
where rn1 = 1 or rn2 = 1
group by deptno;

   2. 需求：有时为了方便打印，会要求多行多列打印，如打印emp.ename列，类似下面这样显示：
    ALLEN JONES MARTIN SMITH    WARD
    BLAKE CLARK KING       SCOTT TURNER
    ADAMS FORD   JAMES     MILLER

    select deptno,listagg(ename,',') within group(order by ename)  
from emp t group by t.deptno;

select wmsys.wm_concat(listagg(ename,',') within group(order by ename))  
from emp t group by t.deptno;

参考：
http://www.2cto.com/database/201304/204096.html
http://www.2cto.com/database/201210/161494.html
http://dacoolbaby.iteye.com/blog/1698957
http://www.itpub.net/thread-1912275-1-1.html

今天的文章listagg函数的用法_db2行转列函数分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/46766.html

listagg函数的用法_db2行转列函数

相关推荐

发表回复