如何按成员数筛选SQL元组

Question

所以我正在处理一个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人的酒吧？

Answer 1

在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)

Answer 2

您需要"GROUP BY"才能使聚合计数工作：

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
   )

Answer 3

"不止一个饮酒者经常光顾。"<>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)