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

最新推荐文章于 2024-09-23 00:05:42 发布

原创最新推荐文章于 2024-09-23 00:05:42 发布 · 886 阅读

1 ·

CC 4.0 BY-SA版权

Hello,Bug

文章标签：

#mysql #数据库 #sql

postgresql 专栏收录该内容

10 篇文章

订阅专栏

该SQL查询用于获取从当前日期往回算的11个月份的统计数据，每个月份的数值通过LEFTJOIN从表名中聚合得出。使用DATE_FORMAT处理日期，并用IFNULL确保无数据时显示0。结果按月份排序。

SELECT 
IFNULL(b.num,0) AS SUM,a.date
 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(需要统计的字段)AS num, DATE_FORMAT(创建时间, '%Y-%m') AS create_time FROM 表名  GROUP BY DATE_FORMAT(创建时间, '%Y-%m')
 ) b   
ON a.date = b.create_time
ORDER BY a.date;