我在MySQL中有3个表.
表:A
+-------------------------------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------------------+--------------+------+-----+---------------------+----------------+
| Ad_Id | int(11) | NO | PRI | NULL | auto_increment |
| User_Id | int(11) | NO | MUL | NULL | |
| Location_Id | int(11) | NO | MUL | NULL | |
| Ad_Date | timestamp | NO | MUL | current_timestamp() | |
| Ad_Title | varchar(255) | NO | MUL | NULL | |
| Ad_Content | mediumtext | NO | MUL | NULL | |
| Ad_Deleted | tinyint(1) | NO | | 0 | |
+-------------------------------------------+--------------+------+-----+---------------------+----------------+
表:州
+----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+----------------+
| State_Id | int(11) | NO | PRI | NULL | auto_increment |
| Country_Id | int(11) | NO | MUL | NULL | |
| State_Name | varchar(45) | NO | | NULL | |
+----------------------+-------------+------+-----+---------+----------------+
表:位置
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| Location_Id | int(11) | NO | PRI | NULL | auto_increment |
| State_Id | int(11) | NO | MUL | NULL | |
| Location_Name | varchar(255) | NO | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
我有这样的疑问:
SELECT Anuncios.Ad_Id, Estado.State_Name, Municipio.Location_Name
FROM Ad AS Anuncios JOIN Location AS Municipio
ON Anuncios.Location_Id = Municipio.Location_Id
JOIN State AS Estado ON Municipio.State_Id = Estado.State_Id
WHERE Anuncios.Ad_Deleted = 0 AND Anuncios.User_Id = 600005;
我的结果是:
+---------+------------+-------------------------------+
| Ad_Id | State_Name | Location_Name |
+---------+------------+-------------------------------+
| 2 | Jalisco | Guadalajara |
| 2 | Jalisco | Tlaquepaque |
| 2 | Jalisco | Tonal? |
| 2 | Jalisco | Guadalajara |
| 2 | Jalisco | Zapopan |
| 2 | Jalisco | Guadalajara |
| 2 | Jalisco | El Salto |
| 2 | Jalisco | Tlaquepaque |
| 2 | Jalisco | Guadalajara |
| 2 | Jalisco | Chapala |
| 2 | Jalisco | Cocula |
| 2 | Jalisco | Tonal? |
| 2 | Jalisco | Tlaquepaque |
| 2 | Jalisco | Zapopan |
| 2 | Jalisco | Guadalajara |
.....
+---------+------------+-------------------------------+
但我不需要这个，我只需要关于LOCATION_NAME(Municipio)的计数，例如:
Guadalajara 48
Zapopan 10
Tlaquepaque 7
....
如何获得这个结果？(我更改了帖子的USER_ID和AD_ID)
谢谢你，我是MySQL新手:}