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

最新推荐文章于 2024-06-12 18:41:43 发布

永远不要低估一颗冠军的心

最新推荐文章于 2024-06-12 18:41:43 发布

阅读量903

点赞数

分类专栏： SQL 工作总结文章标签：数据库 java sql

本文链接：https://blog.csdn.net/weixin_37926633/article/details/127682646

版权

工作总结同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

SQL

6 篇文章 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