开发者

SQL: Only select if minimum number of rows is met

开发者 https://www.devze.com 2023-02-02 05:11 出处:网络
I have two tables: \"Cities\" and \"Submissions\". I want to select a list of cities that have at least n Submissions. What is the best way to go about this? Also relevant is that most of the cities

I have two tables: "Cities" and "Submissions".

I want to select a list of cities that have at least n Submissions. What is the best way to go about this? Also relevant is that most of the cities will have less than n Submissions.

I've tried selecting all the cities and then refiltering the list by doing a COUNT(*) query against submission开发者_JAVA百科s but this is obviously a very inefficient solution. Any help is appreciated.


Try this:

SELECT Cities.id
FROM Cities
JOIN Submissions ON Cities.id = Submissions.CityId
GROUP BY Cities.id
HAVING COUNT(*) >= n


   select cityname, count(submissionid) as submissioncount
   from cities inner join submissions
   on submissions.cityid = cities.cityid
   group by cityname
   having count(submissionid) >= {yourdesiredcount}
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号