Oracle 多行显示在一行详解（listagg）

1 概述

2 示例

2.1 listagg(列1, ‘分隔符’) within group(order by 列2)

with student_info as (
  select 1 sno, '刘一' sname, '女' sex from dual union all
  select 2 sno, '陈二' sname, '女' sex from dual union all
  select 3 sno, '张三' sname, '男' sex from dual union all
  select 4 sno, '李四' sname, '男' sex from dual union all
  select 5 sno, '王五' sname, '女' sex from dual
)
select si.sex,
       count(1),
       listagg(si.sname, ',') within group(order by si.sno) 多行位于一行
  from student_info si
 group by si.sex;

查询结果：（整体：按性别分组）

sex	 count(1)	多行位于一行
男		2		张三,李四
女		3		刘一,陈二,王五

2.2 + over(partition by 列3)

with student_info as (
  select 01 sno, '刘一' sname, '女' sex from dual union all
  select 02 sno, '陈二' sname, '女' sex from dual union all
  select 03 sno, '张三' sname, '男' sex from dual union all
  select 04 sno, '李四' sname, '男' sex from dual union all
  select 05 sno, '王五' sname, '女' sex from dual
)
select si.*,
       listagg(si.sname, ',') within group(order by si.sno) 
         over(partition by si.sex) 多行位于一行
  from student_info si
 order by si.sno;

查询结果：（子查询中：按性别分组）

sno		sname		sex		多行位于一行
1		刘一			女		刘一,陈二,王五		
2		陈二			女		刘一,陈二,王五
3		张三			男		张三,李四
4		李四			男		张三,李四
5		王五			女		刘一,陈二,王五

3 扩展

3.1 listagg() 中的子字符串去重

• Oracle listagg() 中的子字符串去重 regexp_replace

3.2 wm_concat()

/*
  listagg()  ：目前常用方法，返回 字符串类型
  wm_concat()：以前传统方法，返回 clob 类型，效率较低，不推荐
*/
with student_info as (
  select 1 sno, '刘一' sname, '女' sex from dual union all
  select 2 sno, '陈二' sname, '女' sex from dual union all
  select 3 sno, '张三' sname, '男' sex from dual union all
  select 4 sno, '李四' sname, '男' sex from dual union all
  select 5 sno, '王五' sname, '女' sex from dual
)
select si.sex,
       count(1),
       wm_concat(si.sname) 多行位于一行
  from student_info si
 group by si.sex;