当前位置:网站首页>mysql按月查询统计(统计近12个月的项目个数)
mysql按月查询统计(统计近12个月的项目个数)
2022-08-10 15:41:00 【12程序猿】
mysql按月查询统计,没有数据的填充为0
1.获取过去12个月所有的月份
SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
运行结果:
2.把上面的月份与显示数据进行关联查询
-- 统计近12个月的项目个数(project 表没有的月份补齐)
select d.`month`,count(p.id) as `count`
from(
SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
)d
left join project p on DATE_FORMAT(p.plan_start_date, '%Y-%m')=d.`month`
group by month
运行结果:
边栏推荐
- 简述 Mock 接口测试
- SYM32——RTC实时时钟程序讲解
- Oracle数据库备份dmp文件太大,有什么办法可以在备份的时候拆分成多个dmp吗?
- LeetCode-337. House Robber III
- Methodology of multi-living in different places
- photoshop入门教程
- 【21天学习挑战赛】折半查找
- 第贰章模块大全之《 collections模块》
- fastposter v2.9.1 程序员必备海报生成器
- Community News——Congratulations to Dolphin Scheduling China User Group for 9 new "Community Administrators"
猜你喜欢
随机推荐
Mobileye joins hands with Krypton to open a new chapter in advanced driver assistance through OTA upgrade
安克创新每一个“五星好评”背后,有怎样的流程管理?
spark面试常问问题
【芯片】人人皆可免费造芯?谷歌开源芯片计划已释放90nm、130nm和180nm工艺设计套件
商业智能BI行业分析思维框架:铅酸蓄电池行业(二)
LeetCode-876. Middle of the Linked List
2025年推出 奥迪透露将推出大型SUV产品
[Data warehouse design] Why should enterprise data warehouses be layered?(six benefits)
Mobileye携手极氪通过OTA升级开启高级驾驶辅助新篇章
LeetCode-101. Symmetric Tree
scala集合
Exchange Online审计和监控
FP6378AS5CTR SOT-23-5 高效1MHz2A同步降压调节器
关于“算力”,这篇文章值得一看
NFT digital collection development issue - digital collection platform
Please check the preparation guide for the 2022 Huawei Developer Competition
【FreeRTOS】13 动态内存管理
SYM32——RTC实时时钟程序讲解
力扣+牛客--刷题记录
LeetCode_2598_剑指Offer Ⅱ 091.粉刷房子