Oracle中 listagg() within group ()
select deviceid,count(1) as alarmcount,listagg( to_char(b.alarmdsp),';') within group (order by createtime desc) as alarminfo
from biz_technics_opc_alarm a
left join utils_alarm_define b on a.alarmid = b.alarmid
where status = 1
group by deviceid
MySQL中 group_concat
select deviceid,count(1) as alarmcount,group_concat(b.alarmdsp order by createtime desc separator ';') as alarminfo
from biz_technics_opc_alarm a
left join utils_alarm_define b on a.alarmid = b.alarmid
where status = 1
group by deviceid
执行mysql的效果如下:
由于有重复需要去重 加上 distinct 即可达到去重的效果
separator为自定义分割符
select deviceid,count(1) as alarmc