mysql查询当前日期的前12个月的统计

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

上士闻道～勤而行之

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

阅读量764

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/huangli1466384630/article/details/127281821

版权

数据库专栏收录该内容

25 篇文章 2 订阅

订阅专栏

select
ifnull(b.num,0) as sum,a.date, b.plate_number
from
(
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 0 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 2 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 3 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 4 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 5 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 6 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 7 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 8 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 9 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 10 MONTH), '%Y-%m') AS date UNION
SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 11 MONTH), '%Y-%m') AS date
)a
left join
(select sum(real_deliver_cargo_weight) as num,DATE_FORMAT(gmt_created, '%Y-%m') AS create_time, plate_number from waybill group by create_time) b
on a.date = b.create_time
ORDER BY a.date;