ORACLE的wmsys.wm_concat和listagg函数

oracle将多个结果合并展示成一行，有wmsys.wm_concat和listagg函数，之前我习惯用wmsys.wm_concat，但常常遇到有oracle版本不支持的问题。于是转用listagg。

1、wmsys.wm_concat() 连接结果用逗号分隔

select t.type, t.assoCode, 
	   to_char(wmsys.wm_concat(t.date)) date
  from openday t
 where t.type in ('1', '2')
   and t.date >= 20191201
   and t.date <= 20191210
 group by t.type, t.assoCode;

使用wm_concat可能会有字符类型不匹配的问题，需要加个to_char。to_char(wmsys.wm_concat(t.date)) 或者 wmsys.wm_concat(to_char(t.date))

示例：

type	assoCode	date
1	3	20191201,20191208,20191207
1	4	20191208,20191207

2、listagg() within group () 将多条结果合并展示成1行，可以指定分隔符’,'

select t.type, t.assoCode, 
	   listagg(t.date,',') within group(order by t.date) date
  from openday t
 where t.type in ('1', '2')
   and t.date >= 20191201
   and t.date <= 20191210
 group by t.type, t.assoCode;

示例：与wmsys.wm_concat效果基本一致，就是有排序的差别

type	assoCode	date
1	3	20191201,20191207,20191208
1	4	20191207,20191208

3、listagg() within group () over (partition by …) 将多条结果连接，但各自显示一行

select t.type,t.assoCode,
       listagg(t.date,',') within group(order by t.date)over (partition by t.type,t.assoCode) date
 from openday t 
 where t.type in ('1', '2')
      and t.date>=20191201 
      and t.date<=20191210

使用了partition by就不要再group by了。都是分组，前者不去重。
示例：

type	assoCode	date
1	3	20191201,20191207,20191208
1	3	20191201,20191207,20191208
1	3	20191201,20191207,20191208
1	4	20191207,20191208
1	4	20191207,20191208

这种partition by 的方法可以用于既要计数，又要展示的情况：

select * from
(select t.type,t.assoCode,
       listagg(t.trans_date,',') within group(order by t.date)over (partition by t.type,t.assoCode) date
 from openday t 
 where t.type in ('1', '2')
      and t.date>=20191201 
      and t.date<=20191210) v
group by v.type,v.assoCode,v,date

示例：

type	assoCode	date	count
1	3	20191201,20191207,20191208	3
1	4	20191207,20191208	2