如何按月统计 MySQL 中包括当月在内的过去 12 个月的记录数? [英] How to count the number of records in MySQL by month for the last 12 months including the current month?

问题描述

我找到了几个与此类似的问题的答案，但没有一个是 MySQL 独有的.我有一张这样的桌子

I have found several answers for questions similar to this one, but none of then exclusive for MySQL. I have a table like this

+-----------+---------------------+
| requestor | request_date        |
+-----------+---------------------+
| email1    | 2015-08-02 01:03:51 |
----------------------------------
| email2    | 2015-08-01 11:24:32 |
-----------------------------------
| email3    | 2015-07-01 07:35:32 |
-----------------------------------
| email4    | 2015-06-01 16:24:32 |
-----------------------------------
| ...       | ...                 |
-----------------------------------
| email100  | 2014-09-01 22:45:04 |
+-----------+---------------------+

我需要并输出:

+----------+-------+
| Months   | Count |
+----------+-------+
| Aug-2015 | 2     |
--------------------
| Jul-2015 | 1     |
--------------------
| Jun-2015 | 1     |
--------------------
| ...      | ...   |
--------------------
| Sep-2014 | 1     |
+----------+-------+

我尝试使用此代码

SELECT *
 FROM(SELECT DATE_FORMAT(now(), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 MONTH), "%b %Y") as Month
 UNION
 SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 MONTH), "%b %Y") as Month) AS Months
  LEFT JOIN (SELECT DATE_FORMAT(utilization.request_time, "%b %Y") AS Month,      requestor, COUNT(request_time)
        FROM utilization 
        WHERE utilization.request_time > DATE_SUB(now(), INTERVAL 12 MONTH)
        GROUP BY request_time) AS Datas
     ON Months.Month = Datas.Month

推荐答案

SELECT CONCAT(SUBSTRING(DATE_FORMAT(`request_date`, '%M'),1,3),DATE_FORMAT(`request_date`,'-%Y')) as dateName, COUNT(*)
FROM foo
WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(`request_date`, '%Y%m'))<12
GROUP BY YEAR(`request_date`), MONTH(`request_date`)

结果:

dateName    COUNT(*)
Sep-2014    1
Jun-2015    1
Jul-2015    1
Aug-2015    2

这篇关于如何按月统计 MySQL 中包括当月在内的过去 12 个月的记录数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！