Mysql 最近三个月的数据统计

最新推荐文章于 2024-06-05 10:14:30 发布

透过指尖的阳光

最新推荐文章于 2024-06-05 10:14:30 发布

阅读量1.3k

点赞数 1

本文链接：https://blog.csdn.net/weixin_44924188/article/details/119245474

版权

MySQL 数据统计日期查询 LEFT JOIN 统计分析

关键词由CSDN通过智能技术生成

mysql 统计某张表中最近三个月的数据

SELECT
t.year_month as yearMonth,
count(表名.字段名) as count
FROM
(SELECT
DATE_FORMAT(CURDATE(), ‘%Y-%m’) AS year_month
UNION SELECT
DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), ‘%Y-%m’) AS year_month
UNION SELECT
DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), ‘%Y-%m’) AS year_month
) t
LEFT JOIN
表名
on DATE_FORMAT(表名.日期字段名,’%Y-%m’) = t.year_month
GROUP BY t.year_month
ORDER BY t.year_month desc

例如: 统计 user 表中最近三个月的数据

 SELECT
           t.year_month as yearMonth,
           count(u.create_time) as count
       FROM
   	    (SELECT
   	        DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`
           UNION SELECT
               DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`
           UNION SELECT
               DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month`
           ) t
       LEFT JOIN
           user u
       on DATE_FORMAT(u.create_time,'%Y-%m') = t.year_month
       GROUP BY t.`year_month`
       ORDER BY t.`year_month` desc