如何在MySQL多表关联查询中统计Location_Name的出现次数

表结构 #

Ad表 #

+-------------------------------------------+--------------+------+-----+---------------------+----------------+
| 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                   |                |
+-------------------------------------------+--------------+------+-----+---------------------+----------------+

State表 #

+----------------------+-------------+------+-----+---------+----------------+
| 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    |                |
+----------------------+-------------+------+-----+---------+----------------+

Location表 #

+---------------+--------------+------+-----+---------+----------------+
| 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;

需要修改查询，统计每个Location_Name的出现次数，示例结果如下：

Guadalajara 48
Zapopan 10
Tlaquepaque 7
....

修改后的查询 #

使用聚合函数COUNT()结合GROUP BY子句实现统计：

SELECT Estado.State_Name, Municipio.Location_Name, COUNT(*) AS 出现次数
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
GROUP BY Estado.State_Name, Municipio.Location_Name
ORDER BY 出现次数 DESC;

说明 #

1. COUNT(*)：统计每组内的记录数量，即对应地区的广告出现次数
2. GROUP BY Estado.State_Name, Municipio.Location_Name：按州名和地区名分组，避免不同州下的同名地区被错误合并
3. ORDER BY 出现次数 DESC：可选，按出现次数从多到少排序，方便查看高频地区
4. 移除了原查询中的Ad_Id，因为统计场景下不需要每条广告的ID

内容的提问来源于stack exchange，提问作者Alfinoide