Oracle分组后拼接未分组字段
Oracle分组后拼接未分组字段
干货:
listagg( 要拼接的字段名 , 分隔符 ) within group (order by 排序的字段名 )
语句
select 分组字段名 as name,
listagg(要拼接的字段名, ',') within group(order by 排序的字段名),
listagg(要拼接的字段名, ',') within group(order by 排序的字段名2)
from 表名
group by 分组字段名;
ECharts 折线图中使用的语句(解决长度不一致导致数据错乱)
先分组获得等长的数据,然后再分组拼接
select s.ownerofficename as name,
listagg(to_char(s.statisticsdate, 'yyyy-mm-dd'), ',') within group(order by s.statisticsdate) as xAxis,
listagg(s.sumnumber, ',') within group(order by s.statisticsdate) as data
from (select t.ownerofficenumber as ownerofficenumber,
t.ownerofficename as ownerofficename,
t.statisticsdate as statisticsdate,
sum(t.sumnumber) as sumnumber
from etc_handset_statistics_info t
group by t.ownerofficenumber, t.ownerofficename, t.statisticsdate) s
group by s.ownerofficenumber, s.ownerofficename
order by s.OWNEROFFICENUMBER