查询近六个月数据解决方案

淩亂°似流年 2024-04-01 14:35 72阅读 0赞

SELECT DATE\_FORMAT(issue\_date, ‘%m’) issue\_date,amount FROM (SELECT (CASE WHEN issue\_date IS NULL THEN DATE\_FORMAT(DATE\_SUB(CURDATE(), INTERVAL 1 MONTH), ‘%m’) ELSE issue\_date END) issue\_date,(CASE WHEN SUM(amount) IS NULL THEN 0 ELSE SUM(amount) END)amount,(CASE WHEN from\_corp\_no IS NULL THEN ‘NULL0000000000000000’ ELSE from\_corp\_no END) from\_corp\_no FROM t\_issue\_token\_trans WHERE DATE\_FORMAT(issue\_date, ‘%Y%m’) = DATE\_FORMAT(CURDATE(), ‘%Y%m’) AND from\_corp\_no=‘CP201908131123475857’  
UNION ALL SELECT (CASE WHEN issue\_date IS NULL THEN DATE\_SUB(CURDATE(), INTERVAL 1 MONTH) ELSE issue\_date END) issue\_date,(CASE WHEN SUM(amount) IS NULL THEN 0 ELSE SUM(amount) END)amount,(CASE WHEN from\_corp\_no IS NULL THEN ‘NULL0000000000000000’ ELSE from\_corp\_no END) from\_corp\_no FROM t\_issue\_token\_trans WHERE DATE\_FORMAT(issue\_date, ‘%Y%m’) = DATE\_FORMAT(DATE\_SUB(CURDATE(), INTERVAL 1 MONTH), ‘%Y%m’) AND from\_corp\_no=‘CP201908131123475857’  
UNION ALL SELECT (CASE WHEN issue\_date IS NULL THEN DATE\_SUB(CURDATE(), INTERVAL 1 MONTH) ELSE issue\_date END) issue\_date,(CASE WHEN SUM(amount) IS NULL THEN 0 ELSE SUM(amount) END)amount,(CASE WHEN from\_corp\_no IS NULL THEN ‘NULL0000000000000000’ ELSE from\_corp\_no END) from\_corp\_no FROM t\_issue\_token\_trans WHERE DATE\_FORMAT(issue\_date, ‘%Y%m’) = DATE\_FORMAT(DATE\_SUB(CURDATE(), INTERVAL 2 MONTH), ‘%Y%m’) AND from\_corp\_no=‘CP201908131123475857’  
UNION ALL SELECT (CASE WHEN issue\_date IS NULL THEN DATE\_SUB(CURDATE(), INTERVAL 1 MONTH) ELSE issue\_date END) issue\_date,(CASE WHEN SUM(amount) IS NULL THEN 0 ELSE SUM(amount) END)amount,(CASE WHEN from\_corp\_no IS NULL THEN ‘NULL0000000000000000’ ELSE from\_corp\_no END) from\_corp\_no FROM t\_issue\_token\_trans WHERE DATE\_FORMAT(issue\_date, ‘%Y%m’) = DATE\_FORMAT(DATE\_SUB(CURDATE(), INTERVAL 3 MONTH), ‘%Y%m’) AND from\_corp\_no=‘CP201908131123475857’  
UNION ALL SELECT (CASE WHEN issue\_date IS NULL THEN DATE\_SUB(CURDATE(), INTERVAL 1 MONTH) ELSE issue\_date END) issue\_date,(CASE WHEN SUM(amount) IS NULL THEN 0 ELSE SUM(amount) END)amount,(CASE WHEN from\_corp\_no IS NULL THEN ‘NULL0000000000000000’ ELSE from\_corp\_no END) from\_corp\_no FROM t\_issue\_token\_trans WHERE DATE\_FORMAT(issue\_date, ‘%Y%m’) = DATE\_FORMAT(DATE\_SUB(CURDATE(), INTERVAL 4 MONTH), ‘%Y%m’) AND from\_corp\_no=‘CP201908131123475857’  
UNION ALL SELECT (CASE WHEN issue\_date IS NULL THEN DATE\_SUB(CURDATE(), INTERVAL 1 MONTH) ELSE issue\_date END) issue\_date,(CASE WHEN SUM(amount) IS NULL THEN 0 ELSE SUM(amount) END)amount,(CASE WHEN from\_corp\_no IS NULL THEN ‘NULL0000000000000000’ ELSE from\_corp\_no END) from\_corp\_no FROM t\_issue\_token\_trans WHERE DATE\_FORMAT(issue\_date, ‘%Y%m’) = DATE\_FORMAT(DATE\_SUB(CURDATE(), INTERVAL 5 MONTH), ‘%Y%m’) AND from\_corp\_no=‘CP201908131123475857’  
) lastsixmouth WHERE lastsixmouth.from\_corp\_no = ‘CP201908131123475857’ OR lastsixmouth.from\_corp\_no = ‘NULL0000000000000000’ ORDER BY issue\_date DESC