在做数据统计的时候,经常会遇到以每天,每月为周期的统计。如果是使用代码或者SQL进行补零都是很麻烦的事情,这样的话,可以使用一张辅助表来让实现方式变得简单。
建表date_model:
CREATE TABLE `date_model` (
`id` bigint(20) NOT NULL,
`date_str` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在“date_str”字段中保存每天的日期如:2019-08-18
进行查询:
SELECT t.date_str, IFNULL(s.num,0) FROM date_model t LEFT JOIN (
SELECT DATE_FORMAT(b.payment_time,'%Y-%m-%d') as time, SUM(b.price) AS num
FROM date_model a
LEFT JOIN indent b ON a.date_str = DATE_FORMAT(b.payment_time,'%Y-%m-%d')
WHERE b.type = 2 AND b.state = 2
AND b.payment_time >= #{startTime} AND b.payment_time <= #{endTime}
GROUP BY time) s ON t.date_str = s.time
WHERE t.date_str >= #{startTimeStr} AND t.date_str <= #{endTimeStr}
ORDER BY t.date_str DESC
这个SQL是mapper.xml中的样子,其中#{startTime}和#{endTime},是正常的时间字符串,如:“2019-05-05 00:00:00”
#{startTimeStr}和#{endTimeStr}需要在代码中将#{startTime}或者#{endTime}处理成日期,如:“2019-05-05”