为了解决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 TABLEIF    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 ALLSELECT    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    (SELECTDATE_FORMAT(datehour,'%Y-%m-%d %H') datehourFROM    calendar WHERE    ( '2018-11-17' <= date( datehour ) )     AND ( date( datehour ) <= '2018-11-19' ) UNION ALLSELECTDATE_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;