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 |