Oracle列转行函数——listagg()

前言#

今天要帮业务写一个取数的SQL，其中要用到分组函数，并且要取到各个分组内详细的数据，因为用到的比较少，不太清楚要怎么实现，在网上找到listagg函数可以实现，所以记录下它的应用

Listagg()详解#

概述#

listagg()函数可以实现多列记录聚合为一条记录，其实就是列转行；可以单独使用，也可以与GROUP BY一起使用
像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来

基本语法#

LISTAGG(measure_expr,delimiter) WITHIN GROUP( ORDER BY XXX)

• measure_expr ： 需要拼接汇总的列名
• delimiter ： 分隔符，默认NULL
• ORDER BY XXX ： 按照什么排序

示例

点击查看代码

select province, listagg(city, ',') within GROUP (order by city) as Cities
from (select '四川' province, '成都' city from dual
      union all
      select '四川' province, '绵阳' city from dual
      union all
      select '四川' province, '宜宾' city from dual
      union all
      select '山东' province, '济南' city from dual
      union all
      select '山东' province, '青岛' city from dual
      union all
      select '山东' province, '烟台' city from dual) temp
group by province

高级用法#

listagg(XXX, ',') within GROUP (order by XXX) over (partition by XXX) rank

能够通过【partition by】按照不同的层级划分

示例

点击查看代码

select country,
       /* 按照【国家】的层级汇总 */
       listagg(university, ',') within GROUP (order by university) over (partition by country) country_university,
       city,
       /* 按照【城市】的层级汇总 */
       listagg(university, ',') within GROUP (order by university) over (partition by city)    city_university
from (select '中国' country, '北京' city, '北京大学' university from dual union all
      select '中国' country, '北京' city, '清华大学' university from dual union all
      select '中国' country, '上海' city, '同济大学' university from dual union all
      select '中国' country, '上海' city, '同济大学' university from dual union all
      select '英国' country, '伦敦' city, '伦敦商学院' university from dual union all
      select '英国' country, '伦敦' city, '金斯顿大学' university from dual union all
      select '英国' country, '牛津' city, '牛津大学' university
      from dual) temp

拓展#

Mysql group_concat函数#

MySQL中也有类似的函数，就是group_concat函数

基本语法#

group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])

示例

点击查看代码

select country,
        /* 按照【国家】的层级汇总 */
       group_concat(DISTINCT university Order BY university Separator '，') universitys
from (select '中国' country, '北京' city ,'北京大学' university from dual union all
      select '中国' country, '北京' city ,'清华大学' university from dual union all
      select '中国' country, '上海' city ,'复旦大学' university from dual union all
      select '中国' country, '上海' city ,'复旦大学' university from dual union all
      select '英国' country, '伦敦' city ,'伦敦商学院' university from dual union all
      select '英国' country, '伦敦' city ,'金斯顿大学' university from dual union all
      select '英国' country, '牛津' city ,'牛津大学' university from dual) temp
	group by country

注意#

• Mysql的group_concat函数是可以去重的，需要结合GROUP BY使用
• Oracle的Listagg()函数无法进行去重，如果要去重的话，可以使用下列三种方法
  • 使用wm_concat() + distinct去重聚合【oracle官方不太推荐】
  • 使用正则替换方式去重【仅适用于oracle字符串大小比较小的情况】
  • 先去重，再聚合【推荐使用】

参考博客#

• Oracle listagg去重distinct三种方法总结
• Oracle列转行函数 Listagg()详解
• mysql 中 group_concat()用法