按月查询没有月分数据补0 mysql_mysql统计查询按天，月的数据，该天，月没有数据自动补0...

mysql查询最近10天的数据，没有数据自动补0; 正常的数据库中的表, 某一天如果没有数据的话, 以ctime字段为基准进行数据统计就会出现订单量为0的统计不到数据;

原网址：https://blog.csdn.net/sq_com/article/details/84997563

解决方法:

想方设法的创建一张虚拟的数据表, 专门表示日期.如下:

SELECT

tbl._date AS _date,

IFNULL(tbr.totalnum, 0) AS totalnum

FROM

(

SELECT

@s :=@s + 1 AS _index,

DATE(

DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)

) AS _date

FROM

information_schema.tables,

# mysql.help_topic,

# 关联任意一张系统表; 一定的是系统表;

(SELECT @s := - 1) temp

WHERE

@s < 10

ORDER BY

_date

) AS tbl

LEFT JOIN (

SELECT count(*) AS totalnum, DATE(ctime) createdate

FROM zxx_news GROUP BY createdate ) AS tbr ON tbl._date = tbr.createdate

GROUP BY tbl._date ;