理解listagg函数

两道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