查询最近12个月内的数据汇总

最新推荐文章于 2024-02-19 21:45:20 发布

大树丶

最新推荐文章于 2024-02-19 21:45:20 发布

阅读量508

点赞数

分类专栏： java 文章标签： mysql

本文链接：https://blog.csdn.net/qq_27322285/article/details/121770984

版权

java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

select v.`month`,IFNULL(b.countNum,0) countNum
FROM (SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS month
UNION SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS month ) v
LEFT JOIN (select DATE_FORMAT(t.examine_date,'%Y-%m') AS month,count(t.id) as countNum
FROM company t
WHERE DATE_FORMAT(t.examine_date,'%Y-%m') > DATE_FORMAT(date_sub(curdate(), interval 11 month),'%Y-%m') and t.`status` = 1 and t.examine_date is not null group by month )b on v.month = b.month
group by v.month;