MySQL 查询三个月的数据（如：2019-05-27到2019-06-27 2019-04-27到2019-05-27 依次类推 ）...

1、需求：查询三个月的数据量，比如今天是06-27，查询05-27到今天的数据，一次类推

sql:

    SELECT
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) as startdate,
    CURDATE() as enddate,
    SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END ) AS zscum
 FROM event_base_info e
 WHERE e.status='1' and  e.STATE not in('3702820001')  
        and CREATEDATE>=DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
 
    union all
    
    SELECT
    DATE_SUB(CURDATE(), INTERVAL 2 MONTH) as startdate,
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) as enddate,
    SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END ) AS zscum
 FROM event_base_info e
 WHERE e.status='1' and  e.STATE not in('3702820001')  
        and CREATEDATE>=DATE_SUB(CURDATE(), INTERVAL 2 MONTH) and CREATEDATE <=DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
 
 union all
 
     SELECT
    DATE_SUB(CURDATE(), INTERVAL 3 MONTH) as startdate,
    DATE_SUB(CURDATE(), INTERVAL 2 MONTH) as enddate,
    SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END ) AS zscum
 FROM event_base_info e
 WHERE e.status='1' and  e.STATE not in('3702820001')  
        and CREATEDATE>=DATE_SUB(CURDATE(), INTERVAL 3 MONTH) and CREATEDATE <=DATE_SUB(CURDATE(), INTERVAL 2 MONTH)

2、效果

 

如有纰漏，请指正，谢谢。

转载于:https://www.cnblogs.com/w-yu-chen/p/11097058.html