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 ;
图片.png
2.在做报表数据统计的时候，用一个折线图用来显示并统计最近 6 个月内的数据结算情况。为了前端小伙伴方便处理数据，需要将没有数据的月份默认填充为 0
SELECT
CONVERT (t2.year_month_str, CHAR) `timeStamp`,
IFNULL(SUM(i.id), 0) AS total
FROM
(
SELECT
@rownum :=@rownum + 1 AS num,
date_format(
DATE_SUB(now(), INTERVAL @rownum MONTH),
'%Y/%m'
) AS year_month_str
FROM
(SELECT @rownum := - 1) AS r_init,
(
SELECT
c.id
FROM
zxx_news c
LIMIT 6
) AS c_init
) t2
LEFT JOIN zxx_news AS i ON (
CONCAT(
DATE_FORMAT(i.ctime, '%Y'),
'/',
DATE_FORMAT(i.ctime, '%m')
) = t2.year_month_str
AND i.id > 100
)
GROUP BY
t2.year_month_str
图片.png