1、先用一个查询把指定日期范围的日期列表搞出来
-- t_device 为任意表,只需要数据量大于要统计的天数
SELECT
@cdate := date_add(@cdate, INTERVAL - 1 DAY) AS statisName,
0 AS count
FROM
(
SELECT
@cdate := date_add(CURDATE(), INTERVAL + 1 DAY)
FROM
t_device
) tmp
WHERE
@cdate > date_format('2019-09-01 00:00:00', '%Y-%m-%d')
2、业务统计查询也按上述日期查询给统计日期和数量设置别名
-- t_event为要统计数据的表
SELECT
date_format(m.CREATE_TIME, '%Y-%m-%d') AS statisName,
count(*) AS count
FROM
t_event AS m
WHERE
CREATE_TIME >= '2019-09-01 00:00:00'
GROUP BY
date_format(m.CREATE_TIME, '%Y-%m-%d')
3、把两个查询用左连接合起,没数量的日期填0
SELECT
t1.statisName,
COALESCE (t2.count, 0) AS count
FROM
(
SELECT
@cdate := date_add(@cdate, INTERVAL - 1 DAY) AS statisName,
0 AS count
FROM
(
SELECT
@cdate := date_add(CURDATE(), INTERVAL + 1 DAY)
FROM
t_device
) tmp
WHERE
@cdate > date_format('2019-09-01 00:00:00', '%Y-%m-%d')
) t1
LEFT JOIN (
SELECT
date_format(m.CREATE_TIME, '%Y-%m-%d') AS statisName,
count(*) AS count
FROM
t_event AS m
WHERE
CREATE_TIME >= '2019-09-01 00:00:00'
GROUP BY
date_format(m.CREATE_TIME, '%Y-%m-%d')
) t2 ON t1.statisName = t2.statisName
缺点:统计出来的日期没有排序.