mysql - 选择对 table_link 的计数作为多对多关系的桥梁

Question

我有两个表与许多这样的表有关系

第一个:表格请求

MariaDB [ittresnamuda]> select ID_REQUEST, NUMBER_OF_LETTER, TANGGAL_TERIMA FROM tb_requestfix limit 10;
+------------+------------------+---------------------+
| ID_REQUEST | NUMBER_OF_LETTER | TANGGAL_TERIMA      |
+------------+------------------+---------------------+
|         32 |              001 | 2016-01-02 09:00:00 |
|         33 |              002 | 2016-01-02 09:00:00 |
|         34 |              003 | 2016-01-02 13:00:00 |
|         47 |              004 | 2016-01-04 09:00:00 |
|         48 |              005 | 2016-01-04 10:49:00 |
|         52 |              006 | 2016-01-04 11:14:00 |
|         53 |              007 | 2016-01-04 13:50:00 |
|         54 |              008 | 2016-01-04 16:17:00 |
|         55 |              009 | 2016-01-04 16:28:00 |
|         56 |              010 | 2016-01-05 13:12:00 |
+------------+------------------+---------------------+
10 rows in set (0.00 sec)

第二。表项

MariaDB [ittresnamuda]> select * from tb_item_request ;
+---------+-----------+
| ID_ITEM | NAMA_ITEM |
+---------+-----------+
|       1 | Login     |
|       2 | Printer   |
|       3 | Monitor   |
|       4 | Computer  |
|       5 | Network   |
|       6 | Lain-lain |
+---------+-----------+
6 rows in set (0.00 sec)

因此，我创建了一个帮助表来将它们管理为一对多关系。这是表格。

表格链接

MariaDB [ittresnamuda]> select * from tb_link_item_request LIMIT 10;
+-----+------------+---------+
| ID  | id_request | id_item |
+-----+------------+---------+
| 125 |         34 |       6 |
| 146 |         32 |       4 |
| 147 |         33 |       1 |
| 148 |         47 |       4 |
| 153 |         48 |       6 |
| 154 |         52 |       1 |
| 155 |         53 |       2 |
| 156 |         53 |       6 |
| 157 |         54 |       4 |
| 158 |         55 |       3 |
+-----+------------+---------+
10 rows in set (0.00 sec)

现在，我想根据请求的项目、月份和年份统计请求记录，像这样。

+---------+-----------+---------------+
| ID_ITEM | NAMA_ITEM | TOTAL_OF_ITEM |
+---------+-----------+---------------+
|       1 | Login     |       2       |
|       2 | Printer   |       1       |
|       3 | Monitor   |       1       |
|       4 | Computer  |       3       |
|       5 | Network   |       0       |
|       6 | Lain-lain |       3       |
+---------+-----------+---------------+

到目前为止，我是这样尝试的，

MariaDB [ittresnamuda]> SELECT b.ID_ITEM, b.NAMA_ITEM, COUNT(a.id_item) AS TOTAL_ITEM
-> FROM tb_link_item_request a
->
-> INNER JOIN tb_item_request b
-> ON a.id_item = b.ID_ITEM
->
-> INNER JOIN tb_requestfix c
-> ON a.id_request = c.ID_REQUEST
->
-> WHERE a.id_item = 1 AND MONTH(c.TANGGAL_TERIMA) = 01 AND YEAR(c.TANGGAL_TERIMA) = 2016;

+---------+-----------+------------+
| ID_ITEM | NAMA_ITEM | TOTAL_ITEM |
+---------+-----------+------------+
|       1 | LOGIN     |          2 |
+---------+-----------+------------+
1 row in set (0.00 sec)

非常感谢任何帮助，

Answer 1

只需要添加group by子句，去掉where子句即可:

SELECT b.ID_ITEM, b.NAMA_ITEM, YEAR(c.TANGGAL_TERIMA), MONTH(c.TANGGAL_TERIMA), COUNT(a.id_item) AS TOTAL_ITEM
FROM tb_link_item_request a
INNER JOIN tb_item_request b ON a.id_item = b.ID_ITEM
INNER JOIN tb_requestfix c ON a.id_request = c.ID_REQUEST
GROUP BY b.id_item, b.NAMA_ITEM, MONTH(c.TANGGAL_TERIMA) YEAR(c.TANGGAL_TERIMA)