关于计数的SQL问题

发布于 2024-08-02 22:10:19 字数 461 浏览 2 评论 0原文

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

我有一个位置表：

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

以及一个通过 Location_id 连接到位置的地点表

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

我可以像这样加入它们：

SELECTplaces.name,places.id,locations.country,locations.city 来自地点内连接位置 ON place.location_id =locations.id

by 如何才能只获取至少有 50 个地点的城市的结果，并按最大数量对它们进行排序？

谢谢！

原文

I want to make a query so that I can grab only Locations that have at least 50 Places.

I have a table of Locations:

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

And a table of Places connected to Locations by Location_id

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

I can join them like so:

SELECT places.name, places.id, locations.country, locations.city
FROM places
INNER JOIN locations
ON places.location_id = locations.id

by how can I only get the results of cities that have at least 50 places and order them by the largest amount?

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦与时光遇 2024-08-09 22:10:19

使用带有 HAVING 子句的 GROUP BY 子句。

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

Use a GROUP BY with a HAVING clause.

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

回复收藏 0 原文

愚人国度 2024-08-09 22:10:19

好的，我已经看到上面的答案差不多了，但有一些错误，所以只发布正确的版本：

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

OK I've seen that the above answers are almost there but have some mistakes, so just posting the correct version:

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

回复收藏 0 原文

落花随流水 2024-08-09 22:10:19

您可以使用 having 子句通过聚合列的值来限制这些行。另外，MySQL 允许您使用惰性group by，因此您绝对可以利用这一点：

select
    l.country,
    l.city,
    p.name,
    p.details,
    count(*) as number_of_places
from
    locations l
    inner join places p on
        l.id = p.location_id
group by
    l.id
having
    count(*) >= 50
order by
    number_of_places,
    l.country,
    l.city,
    p.name

You can use the having clause to limit these rows by the value of an aggregate column. Also, MySQL allows you to use lazy group bys, so you can absolutely take advantage of this:

select
    l.country,
    l.city,
    p.name,
    p.details,
    count(*) as number_of_places
from
    locations l
    inner join places p on
        l.id = p.location_id
group by
    l.id
having
    count(*) >= 50
order by
    number_of_places,
    l.country,
    l.city,
    p.name

回复收藏 0 原文

只为守护你 2024-08-09 22:10:19

性能有些不佳，但应该可以：

选择places.name、places.id、sq.country、sq.city、sq.counter
FROM（SELECT Locations.id、国家、城市、count(*) 作为计数器 FROM Locations
加入地点 ON (Locations.Id=Places.Location_id)GROUP BYlocations.id HAVING count(*) >= 50) AS sq加入地点 ON (sq.id=Places.Location_id)ORDER BY 计数器 DESC`

PS 确切的语法可能会因数据库而异，我不确定这是否与 mysql 兼容。