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

文章目录

• 1 概述
• 2 方法
• • 2.1 listagg()
• 3 扩展
• • 3.1 listagg() 中的子字符串去重
  • 3.2 wm_concat()

1 概述

1. 多行显示在一行常用方法 listagg()
  (1) listagg(列名1, '分隔符') within group(order by 列名2)  -- 分组函数
  (2) + over(partition by 列3)                  -- 追加该子句 = 一般函数  
2. 说明
  (1) '分隔符'    ：如 ','
  (2) listagg()  ：目前常用方法，返回 字符串类型
      wm_concat()：以前传统方法，返回 clob 类型，效率较低，不推荐 

 
 

2 方法

2.1 listagg()

方式：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：+ 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()

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;