MySQL实现按天分组统计，提供完整日期列表，无数据自动补0

╰半橙微兮° 2022-01-29 17:51 341阅读 0赞

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

![20190527202711738.png][]

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

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

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

![20190527203532233.png][]

# MySQL实现按天分组统计，提供完整日期列表，无数据自动补0 #

SELECT
                        DATE_FORMAT(CURDATE() - INTERVAL (a.n + (10 * b.n)) MONTH, '%Y%m') AS date
                    FROM (
                        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                    ) AS a CROSS JOIN (
                        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                    ) AS b
                    WHERE (CURDATE() - INTERVAL (a.n + (10 * b.n)) MONTH) > (CURDATE() - INTERVAL 24 MONTH)

## MySQL实现按月分组统计，提供完整日期列表，无数据自动补0 ##

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

[20190527202711738.png]: /images/20220129/c2d9128f18994c40846bd9c7308cc611.png
[20190527203532233.png]: /images/20220129/4ca60aa08bd54a498ed642ffdedec969.png