list()函数的作用_函数有界的定义

大家好，欢迎来到IT知识分享网。

两道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函数功能类似。
简单的说就是在分组合并后，把某列数据逐个枚举出来，其实也是一个行转列的效果。

如下,原始数据：

list()函数的作用_函数有界的定义

实现效果：

list()函数的作用_函数有界的定义

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;

list()函数的作用_函数有界的定义

–利用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;

list()函数的作用_函数有界的定义

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;

list()函数的作用_函数有界的定义

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;

list()函数的作用_函数有界的定义

   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;

list()函数的作用_函数有界的定义

参考：
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

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://yundeesoft.com/29756.html

list()函数的作用_函数有界的定义

相关推荐

发表回复