MySQL按日统计数据，时间范围内无数据补0
2021-01-09 本文已影响0人
lanmoyingsheng
一般管理后台都会有一些按日统计的需求，但是经常某些日期没有数据。
数据量大的时候，考虑性能还是建议在后端代码填充这些数据0，如果数据量不大，可以把这个逻辑放到sql中
SELECT SUM(amount) amount, format_day from
(SELECT 0 amount, DATE_ADD('2021-01-01', INTERVAL ( @i := @i + 1 )- 1 DAY ) format_day
FROM information_schema.columns, (SELECT @i := 0 ) d
WHERE DATE_ADD('2021-01-01', INTERVAL @i DAY ) BETWEEN '2021-01-01' AND '2021-02-01'
UNION ALL
SELECT amount, format_day
FROM t_block_invest_record
WHERE format_day >= '2021-01-01' AND format_day <= '2021-02-01') e
GROUP BY format_day
ORDER BY format_day
执行结果如下，为连续的日期数据。
image.png
这样后端代码会干净很多。
需要注意的是，框架使用Hibernate解析 :=时会有异常，:是Hibernate类似关键字的存在，需要做一些处理。在 :=前加两个转义字符。
\\:=
参考： How can I use MySQL assign operator(:=) in hibernate native query?
Can't use := for variable assignment within a SQL-Statement
