如何按成员数量过滤 SQL 元组
所以我正在解决一个 SQL 问题,其中我有一个酒吧和啤酒数据库,我想从中: 列出所有提供迈克喜欢的啤酒并且经常有多个饮酒者光顾的酒吧。
数据库包括:
likes (drinker, beer)
frequents(dinker, bar)
sells(bar, beer)
所以我尝试了:
SELECT bar
FROM beer.sells
WHERE beer IN
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') AND bar IN
(SELECT bar FROM beer.frequents HAVING COUNT(drinker) > 1)
哪个不起作用...为什么计数(饮酒者)比较不能作为过滤器来过滤掉经常光顾的饮酒者少于 2 人的酒吧?
So I'm working on an SQL problem, in which I have a bars and beer database from which I want to:
list all bars which serve a beer that Mike likes, and which are frequented by more than one drinker.
The database consists of:
likes (drinker, beer)
frequents(dinker, bar)
sells(bar, beer)
So I tried:
SELECT bar
FROM beer.sells
WHERE beer IN
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') AND bar IN
(SELECT bar FROM beer.frequents HAVING COUNT(drinker) > 1)
Which didn't work... why doesn't the count(drinker) comparison work as a filter to filter out bars that have less than 2 drinkers who frequent them?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

发布评论
评论(3)
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
看起来您需要在 bar 子查询中使用
GROUP BY bar
。否则,您将有效地从beer.frequents
获取COUNT(*)
,这不是您想要的:Looks like you need a
GROUP BY bar
in the bar subquery. Otherwise, you'll effectively getCOUNT(*)
frombeer.frequents
, which isn't what you want: