其实还可以这样;
mysql查询最近7天的数据,没有数据自动补0; 正常的数据库中的表, 某一天如果没有数据的话, 以creattime字段为基准进行数据统计就会;出现订单量为0的 统计不到;
解决方法:
1. 就是想方设法的创建一张虚拟的数据表, 专门表示日期.如下:
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 < 6
ORDER BY
_date
) AS tbl
LEFT JOIN (
SELECT count(id) AS totalnum, DATE(createtime) createdate
FROM orderinfo GROUP BY createdate ) AS tbr ON tbl._date = tbr.createdate
GROUP BY tbl._date ;
############################################
SQL 如上, 也算是一个小小的技巧吧; 简单记录一下.