MySQL按天统计

mysql数据库，当我们按日期展示数据时，经常碰到日期时间段内某一天数据不存在，为空。这时候我们查询数据往如下图一样，时间数据发生间断，造成数据缺失，无法连续展示

所以可以加上下面的代码，进行一个链接查询，以时间为链接条件，可以将间断数据补全

  ｓｅｌｅｃｔ DATE_FORMAT( CURDATE( ) - INTERVAL ( a.n + ( 10 * b.n ) + ( 100 * c.n ) ) DAY, ‘%Y%m%d‘ ) AS date FROM ( ｓｅｌｅｃｔ 0 AS n UNION ALL ｓｅｌｅｃｔ 1 UNION ALL ｓｅｌｅｃｔ 2 UNION ALL ｓｅｌｅｃｔ 3 UNION ALL ｓｅｌｅｃｔ 4 UNION ALL ｓｅｌｅｃｔ 5 UNION ALL ｓｅｌｅｃｔ 6 UNION ALL ｓｅｌｅｃｔ 7 UNION ALL ｓｅｌｅｃｔ 8 UNION ALL ｓｅｌｅｃｔ 9 ) AS a CROSS JOIN ( ｓｅｌｅｃｔ 0 AS n UNION ALL ｓｅｌｅｃｔ 1 UNION ALL ｓｅｌｅｃｔ 2 UNION ALL ｓｅｌｅｃｔ 3 UNION ALL ｓｅｌｅｃｔ 4 UNION ALL ｓｅｌｅｃｔ 5 UNION ALL ｓｅｌｅｃｔ 6 UNION ALL ｓｅｌｅｃｔ 7 UNION ALL ｓｅｌｅｃｔ 8 UNION ALL ｓｅｌｅｃｔ 9 ) AS b CROSS JOIN ( ｓｅｌｅｃｔ 0 AS n UNION ALL ｓｅｌｅｃｔ 1 UNION ALL ｓｅｌｅｃｔ 2 UNION ALL ｓｅｌｅｃｔ 3 UNION ALL ｓｅｌｅｃｔ 4 UNION ALL ｓｅｌｅｃｔ 5 UNION ALL ｓｅｌｅｃｔ 6 UNION ALL ｓｅｌｅｃｔ 7 UNION ALL ｓｅｌｅｃｔ 8 UNION ALL ｓｅｌｅｃｔ 9 ) AS c WHERE ( CURDATE( ) - INTERVAL ( a.n + ( 10 * b.n ) + ( 100 * c.n ) ) DAY ) > ( CURDATE( ) - INTERVAL 24 MONTH )

以该语句进行连接查询，如图

MySQL实现按天分组统计，提供完整日期列表，无数据自动补0  ｓｅｌｅｃｔ DATE_FORMAT(CURDATE() - INTERVAL (a.n + (10 * b.n)) MONTH, ‘%Y%m‘) AS date FROM ( ｓｅｌｅｃｔ 0 AS n UNION ALL ｓｅｌｅｃｔ 1 UNION ALL ｓｅｌｅｃｔ 2 UNION ALL ｓｅｌｅｃｔ 3 UNION ALL ｓｅｌｅｃｔ 4 UNION ALL ｓｅｌｅｃｔ 5 UNION ALL ｓｅｌｅｃｔ 6 UNION ALL ｓｅｌｅｃｔ 7 UNION ALL ｓｅｌｅｃｔ 8 UNION ALL ｓｅｌｅｃｔ 9 ) AS a CROSS JOIN ( ｓｅｌｅｃｔ 0 AS n UNION ALL ｓｅｌｅｃｔ 1 UNION ALL ｓｅｌｅｃｔ 2 UNION ALL ｓｅｌｅｃｔ 3 UNION ALL ｓｅｌｅｃｔ 4 UNION ALL ｓｅｌｅｃｔ 5 UNION ALL ｓｅｌｅｃｔ 6 UNION ALL ｓｅｌｅｃｔ 7 UNION ALL ｓｅｌｅｃｔ 8 UNION ALL ｓｅｌｅｃｔ 9 ) AS b WHERE (CURDATE() - INTERVAL (a.n + (10 * b.n)) MONTH) > (CURDATE() - INTERVAL 24 MONTH)MySQL实现按月分组统计，提供完整日期列表，无数据自动补0

上述是俩个例子，大家可以根据具体情况自由组合发挥。上述是目前楼主发现的方法，可能还有其他更好方法，希望可以分享给楼主我，谢谢