【记录一个问题】MariaDB 10.1.9 中with rollup的神器问题

我想统计每天数据的处理状态的汇总，于是写了这样一条语句：

1 select * from (
2 select ifnull(date(update_time),'_30天汇总') as dtm, ifnull(b.status_name, '_当天汇总') as status_name,count(1) as cnt
3 from `t_workflow` a left outer join dict_status b on a.status=b.status 
4 where a.update_time>=DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
5 group by date(update_time),b.status_name with rollup 
6 ) a order by 1 desc, 2 ASC

神奇的事情发生了，当天汇总的数据居然出现了两条：

最后发现是ifnull的问题，修改成如下后正确：

1 select * from (
2 select date(update_time) as dtm, b.status_name as status_name,count(1) as cnt
3 from `t_workflow` a left outer join dict_status b on a.status=b.status 
4 where a.update_time>=DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
5 group by date(update_time),b.status_name with rollup 
6 ) a order by 1 desc, 2 ASC

 

转载于:https://www.cnblogs.com/ahfuzhang/p/10885924.html