一、
这个写法和wm_concat相似,
listagg(day,',')要把哪一列转换为同一行
within group (order by day)同一行如何排序
with temp as
(
select '1月' month, '1' day from dual union all
select '2月' month, '1' day from dual union all
select '2月' month, '2' day from dual union all
select '3月' month, '1' day from dual union all
select '3月' month, '2' day from dual union all
select '3月' month, '3' day from dual
)
select
month,listagg(day,',')within group (order by day ) days
from temp
group by month;
--去重复
regexp_replace((listagg(o.USER_NAME, '、') within group(order by s.start_date asc)), '([^、]+)(、\1)+', '\1') JY_NAME,
with temp as
(
select '1月' month, '1' day from dual union all
select '2月' month, '1' day from dual union all
select '2月' month, '2' day from dual union all
select '3月' month, '1' day from dual union all
select '3月' month, '2' day from dual union all
select '3月' month, '3' day from dual
)
select
month,
to_char(wm_concat(day))
from temp
group by month;
二、
不使用group by
with temp as
(
select '1月' month, '1' day from dual union all
select '2月' month, '1' day from dual union all
select '2月' month, '2' day from dual union all
select '3月' month, '1' day from dual union all
select '3月' month, '2' day from dual union all
select '3月' month, '3' day from dual
)
select month ,
listagg(day,',')within group (order by day) over (partition by month) DAYS
from temp
with temp as
(
select '1月' month, '1' day from dual union all
select '2月' month, '1' day from dual union all
select '2月' month, '2' day from dual union all
select '3月' month, '1' day from dual union all
select '3月' month, '2' day from dual union all
select '3月' month, '3' day from dual
)
select *
from (select month,
to_char(wm_concat(day) over(partition by month order by day)) days,
row_number() over(partition by month order by day desc) rn
from temp)
where rn = 1