为了解决mysql按日期分组查询统计的时候，没有数据补0。可以生成连续的时间表格来辅助查询
* 生成按天的数据
* 每一个小时为一个分段 生成如下辅助表
*代码如下
CREATE TABLE num ( i INT );
-- 创建一个表用来储存0-9的数字
INSERT INTO num ( i )
VALUES
( 0 ),
( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 ),
( 6 ),
( 7 ),
( 8 ),
( 9 ); -- -------------------------------------------------------------- CREATE TABLE
IF
NOT EXISTS calendar ( datelist date, datehour datetime ); -- --------------------------------------------------------------
INSERT INTO calendar ( datelist ) SELECT -- 生成按天的数据
adddate( ( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT( "2018-11-10", '%Y-%m-%d' ) ), numlist.id ) AS `date`
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
CROSS JOIN num AS n10000
) AS numlist; -- --------------------------------------------------------------
INSERT INTO calendar ( datehour ) SELECT -- 生成按小时的数据
DATE_ADD( ( DATE_FORMAT( "2018-11-10", '%Y-%m-%d' ) ), INTERVAL numlist.id HOUR ) AS `date`
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
CROSS JOIN num AS n10000
) AS numlist;
* 按天查询
SELECT
date( datelist ) datas,
count( * ) - 1 AS counts
FROM
(
SELECT
datelist
FROM
calendar
WHERE
( '2018-11-17' <= date( datelist ) )
AND ( date( datelist ) <= '2018-11-19' ) UNION ALL
SELECT
createtime
FROM
snap_face
LEFT JOIN device ON snap_face.deviceID = device.id
WHERE
( '2018-11-17' <= date( snap_face.createtime ) )
AND ( date( snap_face.createtime ) <= '2018-11-19' )
) temp
GROUP BY
datas
ORDER BY
datas;
* 按小时查询
SELECT
DATE_FORMAT(datehour,'%Y-%m-%d %H') datas,
count( * ) - 1 AS counts
FROM
(
SELECT
DATE_FORMAT(datehour,'%Y-%m-%d %H') datehour
FROM
calendar
WHERE
( '2018-11-17' <= date( datehour ) )
AND ( date( datehour ) <= '2018-11-19' ) UNION ALL
SELECT
DATE_FORMAT(createtime,'%Y-%m-%d %H')
FROM
snap_face
LEFT JOIN device ON snap_face.deviceID = device.id
WHERE
( '2018-11-17' <= date( snap_face.createtime ) )
AND ( date( snap_face.createtime ) <= '2018-11-19' )
) temp
GROUP BY
datas
ORDER BY
datas;