mysql - 获取每个城市的答案列表

Question

我有这个数据库:

用户表:

+----+---------+
| id | city_id |
+----+---------+

city_id -> references id on city

城市表:

+----+------+
| id | city |
+----+------+

答案表:

+----+---------+
| id | user_id |
+----+---------+

user_id -> references id on users

现在我想要一个带有这个结果的查询:

+-----------+--------+
| City-name | amount |
+-----------+--------+
| New York  | 500    |
+-----------+--------+

我尝试了几次连接、计数、直觉但没有成功。认为这应该很容易，但没有做到。

我的查询:

SELECT cities.name, COUNT(answers.id) FROM answers
JOIN users ON users.id = answers.user_id
JOIN cities on users.city_id = cities.id

但只给了我 1 个城市..

Answer 1

您需要添加GROUP BY cities.name

SELECT cities.name, COUNT(answers.id) FROM answers
JOIN users ON users.id = answers.user_id
JOIN cities on users.city_id = cities.id
GROUP BY cities.name

这将告诉 MySQL 您希望 COUNT 按城市分组。如果没有这个，mysql 会将所有内容组合在一起并全部计数。