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、效果
如有纰漏,请指正,谢谢。