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 ;