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

最新推荐文章于 2024-08-12 11:40:22 发布

panghaomingme

最新推荐文章于 2024-08-12 11:40:22 发布

阅读量3.2k

点赞数 2

分类专栏：数据库 sql学习

本文链接：https://blog.csdn.net/panghaomingme/article/details/82910390

版权

数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

sql学习

3 篇文章 0 订阅

订阅专栏

对表中每个季度的数据进行统计并实现自动补零，首先先查询出表中每个季度的数据

SELECT QUARTER(applicationdate) as mon, COUNT(*) as con FROM qm_technical_zero GROUP BY QUARTER(applicationdate)

从表qm_technical_zero中按季度查询日期applicationdate的数据，但是只能出现前三季度的数据，第四季度为零所以不显示。

结果如下所示

然后在建立一个表

SELECT  1 as click_date UNION ALL
SELECT  2 as click_date UNION ALL
SELECT  3 as click_date UNION ALL
SELECT  4 as click_date

结果如下图所示

然后对两个表进行联合查询就可以实现补零操作。

SELECT click_date as q,IFNULL(b.con,0) as c FROM(SELECT  1 as click_date UNION ALL
SELECT  2 as click_date UNION ALL
SELECT  3 as click_date UNION ALL
SELECT  4 as click_date) a LEFT JOIN(
SELECT QUARTER(applicationdate) as mon, COUNT(*) as con FROM qm_technical_zero GROUP BY QUARTER(applicationdate))
b ON click_date=b.mon

进行联合查询后结果如下所示：