我有一个日历表,它是这样的
c_calendardate
1/1/2015
2/3/2015
.
.
.
10/18/2015
10/19/2015
和材料请求的交易记录。
+------------+------------+-------------+-----------+----------+
| 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 |
+------------+-----------------------+
上面的输出是每个月统计每个打开的项目,如果记录仍然没有被批准,每个月仍然会被统计,有人可以帮助我查询吗?
您可以使用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');
您可以这样尝试
SELECT count(id),
STR_TO_DATE(Date_Entry, '%m/%d/%Y') as date1
MONTHNAME(STR_TO_DATE(Date_Entry, '%m/%d/%Y') ),
YEAR(STR_TO_DATE(Date_Entry, '%m/%d/%Y') )
FROM
Table_name
WHERE
Status = 'open'
group by date1