MySQL中查询过去12个月的数据.补全12个月,数据为空则补0

最新推荐文章于 2024-07-27 02:04:32 发布

Ludwig__

最新推荐文章于 2024-07-27 02:04:32 发布

阅读量1.4k

点赞数 1

分类专栏： java学习中的问题文章标签： mysql java sql 数据库 oracle

本文链接：https://blog.csdn.net/qq_46190347/article/details/115481283

版权

java学习中的问题专栏收录该内容

46 篇文章 0 订阅

订阅专栏

– 1.创建现在到过去的12个月的视图

CREATE VIEW past_12_month_view AS

SELECT DATE_FORMAT(CURDATE(), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), ‘%Y-%m’) AS month

UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), ‘%Y-%m’) AS month;

– 2.自己需要的查询语句

	SELECT 
    DATE_FORMAT(gmt_create,'%Y-%m') as pastTime,
    COUNT(a.gmt_create) as Num 
    from 
    j_op_member a 
    where 
    a.is_new = 1 
    GROUP BY 
    DATE_FORMAT(gmt_create,'%Y-%m')

– 3.使用左链接查询

	select 
    t.month as month,
    ifNull(b.Num,0) as count 
    from past_12_month_view t 
    LEFT JOIN
    -- 填入自己的查询语句
    (SELECT 
    DATE_FORMAT(gmt_create,'%Y-%m') as pastTime,
    COUNT(a.gmt_create) as Num 
    from 
    j_op_member a 
    where 
    a.is_new = 1 
    GROUP BY 
    DATE_FORMAT(gmt_create,'%Y-%m')) b
    on b.pastTime=t.month;