mysql - SQL 每月在未批准的情况下对记录中的未清项进行计数

Question

我有一个日历表，就是这个。

c_calendardate
1/1/2015
2/3/2015
.
.
.
10/18/2015
10/19/2015

以及 Material 请求的交易记录。

+------------+------------+-------------+-----------+----------+
| Requestor  |   Item     | Date_Entry  | Approver  |  Status  |
+------------+------------+-------------+-----------+----------+
| Bob        | PCB        | 1/2/2015    | Admin     | Open     |
| Bob        | PCB        | 3/19/2015   | Admin     | Approved |
| May        | UTP Cable  | 1/1/2015    | Admin     | Open     |
| May        | UTP Cable  | 4/20/2015   | Admin     | Approved |
| Joseph     | RJ45       | 2/10/2015   | Admin     | Open     |
| Joseph     | RJ46       | 6/3/2015    | Admin     | Approved |
+------------+------------+-------------+-----------+----------+

我想要的输出是这个。

+------------+-----------------------+
|   Month    | Total_Open_Each_Month |
+------------+-----------------------+
| 2015-Jan   |                     2 |
| 2015-Feb   |                     3 |
| 2015-Mar   |                     3 |
| 2015-Apr   |                     2 |
| 2015-May   |                     1 |
| 2015-Jun   |                     1 |
| 2015-Jul   |                     0 |
| 2015-Aug   |                     0 |
| 2015-Sept  |                     0 |
| 2015-Oct   |                     0 |
| 2015-Nov   |                     0 |
| 2015-Dec   |                     0 |
+------------+-----------------------+

上面的输出是每个月统计每个未清项，如果记录还没有被批准，仍然会按每个月统计，谁能帮我查询一下？

Answer 1

您可以使用DATE_FORMAT() 和GROUP BY

SELECT 
 DATE_FORMAT(STR_TO_DATE(c_calendardate,'%m/%d/%Y'),'%Y-%b'), 
 COUNT(status) 
FROM yourcalendar 
 LEFT JOIN materialrequest on (c_calendardate=Date_Entry AND status='Open')
GROUP BY DATE_FORMAT(STR_TO_DATE(c_calendardate,'%m/%d/%Y'),'%Y-%b');