SQL关于计数的问题

Question

我想进行查询，以便我只能获取至少有 50 个地点的位置。

我有一张位置表:

Id, City, Country
1, Austin, USA
2, Paris, France

以及通过 Location_id 连接到 Locations 的 Places 表

Id, Name, Details, Location_id
1, The Zoo, blah, 2
2, Big Park, blah, 2

我可以像这样加入他们:

SELECT places.name, places.id, locations.country, locations.city从地方INNER JOIN 位置ON places.location_id = locations.id

如何只得到至少有50个地点的城市的结果，并按最大数量排序？

谢谢!

Answer 1

使用 GROUP BY with a HAVING条款。

SELECT locations.country, locations.city, COUNT(*)
FROM places
     INNER JOIN locations ON places.location_id = locations.id
GROUP BY locations.country, locations.city
HAVING COUNT(*) >= 50