sql查询每个月的数据并自动补零

使用sql查询，对每个月的数据进行查询并进行统计，当该月数据为零时进行补零操作。

首先先从表qm_technical_zero中对每个月的数据进行查询

SELECT COUNT(*) as con , CONCAT(YEAR(applicationdate),'-',MONTH(applicationdate)) as mon FROM `qm_technical_zero` GROUP BY mon

查询结果如下所示

从查询结果我们可以看出2018年4月9月10月11月12月的数据都是为零的，但是却没有显示。因此我们需要将数据进行补零处理。

我们需要建立一个有当年所有月份的表，建表过程如下

 

SELECT  STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d') as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 1 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 2 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 3 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 4 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 5 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 6 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 7 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 8 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 9 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 10 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 11 MONTH) as click_date

通过建表我们可以得到如下所示的表，包含了当前年份的所有月份

然后我们再对这两张表进行联合查询

SELECT CONCAT(YEAR(click_date),'-',MONTH(click_date)) as m,IFNULL(b.con,0) as c FROM(SELECT  STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d') as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 1 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 2 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 3 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 4 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 5 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 6 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 7 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 8 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 9 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 10 MONTH) as click_date UNION ALL
SELECT  DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-',1,'-',1) , '%Y-%m-%d'),INTERVAL 11 MONTH) as click_date) a LEFT JOIN(
SELECT COUNT(*) as con , CONCAT(YEAR(applicationdate),'-',MONTH(applicationdate)) as mon FROM `qm_technical_zero` GROUP BY mon)
b ON CONCAT(YEAR(click_date),'-',MONTH(click_date))=b.mon

联合查询的结果如下所示

从结果中我们可以看出，经过联合查询之后，对4月、9月等数据为零的月份进行了补零操作。

参考博客：https://blog.csdn.net/ouyang111222/article/details/77638826