如何按成员数量过滤 SQL 元组

发布于 01-05 10:20 字数 446 浏览 2 评论 0原文

所以我正在解决一个 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

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

评论(3

2025-01-12 10:20:01

看起来您需要在 bar 子查询中使用 GROUP BY bar 。否则,您将有效地从 beer.frequents 获取 COUNT(*),这不是您想要的:

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') AND bar IN
(SELECT bar FROM beer.frequents GROUP BY bar HAVING COUNT(drinker) >= 2)

Looks like you need a GROUP BY bar in the bar subquery. Otherwise, you'll effectively get COUNT(*) from beer.frequents, which isn't what you want:

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') AND bar IN
(SELECT bar FROM beer.frequents GROUP BY bar HAVING COUNT(drinker) >= 2)
瞎闹2025-01-12 10:20:01

您需要“GROUP BY”才能使聚合 COUNT 发挥作用:

SELECT bar
 FROM beer.sells
 WHERE beer IN 
  (SELECT beer
    FROM beer.likes
    WHERE drinker = 'Mike'
  )
 AND bar IN
  (SELECT bar
    FROM beer.frequents
    GROUP BY bar
    HAVING COUNT(drinker) > 1
   )

You need to 'GROUP BY' for your aggregated COUNT to work:

SELECT bar
 FROM beer.sells
 WHERE beer IN 
  (SELECT beer
    FROM beer.likes
    WHERE drinker = 'Mike'
  )
 AND bar IN
  (SELECT bar
    FROM beer.frequents
    GROUP BY bar
    HAVING COUNT(drinker) > 1
   )
遥远的她2025-01-12 10:20:01

“有多个一个饮酒者经常光顾。” <> 有计数(饮酒者)> 2 (1)

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') 
AND bar IN
(SELECT bar 
 FROM beer.frequents 
 GROUP BY bar
 HAVING COUNT(drinker) > 1)

"which are frequented by more than one drinker." <> HAVING COUNT(drinker) > 2 (1)

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') 
AND bar IN
(SELECT bar 
 FROM beer.frequents 
 GROUP BY bar
 HAVING COUNT(drinker) > 1)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文