按天统计数据，如果当天没有记录，则统计为0

最新推荐文章于 2021-01-19 03:53:21 发布

最新推荐文章于 2021-01-19 03:53:21 发布 · 1.2k 阅读

文章标签：

本文介绍了一种在MySQL中实现按天统计特定表记录数的方法，并通过左连接(left join)来填充缺失日期的技巧，确保统计结果连续且完整。

今天做一个统计需求，按天统计这张表的记录数，按照pay_month和格式化之后的payment_time来分组，这张表大致数据如下
[img]http://dl2.iteye.com/upload/attachment/0114/5055/524040d7-c44c-3575-af7f-ab95ed67b85a.png[/img]

按照pay_month和格式化之后的payment_time分组之后，数据如下

[img]http://dl2.iteye.com/upload/attachment/0114/5059/fec56638-7a73-352a-bd8c-3d0f9693a0b5.png[/img]

我需要统计的是10天前到今天的数据，但就pay_month=1的数据来讲，只有2，3，4，7号有数据。而我想要没有数据的那一天同样统计出来并用0来填充。
第一反应是用left join，但由于mysql没有oracle的connect by level，只能用一张数据条数绝对大于10的表来代替select level from dual connect by level < 10;我就选了tb_order这张表本身

SELECT @cdate := date_add(@cdate,interval -1 day) days from 
(SELECT @cdate := CURDATE() from tb_order limit 10) t1

[img]http://dl2.iteye.com/upload/attachment/0114/5061/9265c8ff-416d-3514-a2bc-fc9268bd5677.png[/img]
再left join需要分组的表

select t2.days,t3.begin_day,t3.end_day,t3.pay_month,
			 t3.pay_day,IFNULL(t3.score,0) total_score from 
	(SELECT @cdate := date_add(@cdate,interval -1 day) days from 
		(SELECT @cdate := CURDATE() from tb_order limit 10) t1) t2
LEFT JOIN
	(select CURDATE() end_day,PAY_MONTH,
	 date_add(CURDATE(),interval -10 day) begin_day,
	 date_format(PAYMENT_TIME,'%Y-%m-%d') pay_day,
	 count(1) score from tb_order
	where ORDER_STATUS='ORDER_USED' 
	and CURDATE()<=date_add(date_format(PAYMENT_TIME,'%Y-%m-%d'),interval 10 day)
	group by PAY_MONTH,date_format(PAYMENT_TIME,'%Y-%m-%d')) t3
on t2.days=t3.pay_day

最终达到的结果如下：

[img]http://dl2.iteye.com/upload/attachment/0114/5065/7f12145e-6eb6-3048-a894-e5f2806b1e66.png[/img]

正真需要的只有days和total_score两列，这样就算是完成了。