mysql 查询 - 查找包含所有元素 B 的元素 A

Question

困境:此查询实现“查找所有喜欢 Caravan 出售的至少一种啤酒的饮酒者”

“IN”关键字的作用就像一个巨大的 OR 语句。

目标查询是:找到喜欢 Caravan 提供的每种啤酒的饮酒者

SELECT drinker 
FROM BarBeerDrinker.likes
WHERE BarBeerDrinker.likes.beer IN (
        SELECT beer
        FROM BarBeerDrinker.sells
        WHERE BarBeerDrinker.sells.bar = "Caravan"
    );

架构:

喜欢表

     drinker    beer
     John   "Blue Moon"
     Mike   "Blue Moon"
     John   “Budweiser”
     Mike   “Budweiser”
     John   "Creamy Dark"
     Mike   "Creamy Dark"
     Mike   “Hefeweizen”
     John   “Killian's”
     John   "Michelob Golden Draft"
     Mike   "Michelob Golden Draft Light"
     John   "Original Premium"
     Mike   "Original Premium Lager"
     Jesse  "Original Premium Lager Dog"
     Mike   "Original Premium Lager Dog"
     John   "Stolichnaya Citrona"

<小时/>

销售表

   bar  beer
   "A.P. Stump's"   Hefeweizen
   "Blue Angel" "Hefeweizen Doppelbock"
   "Blue Angel" "Original Premium Lager Dog"
   "Blue Tattoo"    Killian's
   "Britannia Arms" Budweiser
   Cabana   Budweiser
   Caravan  Budweiser
   Caravan  "Original Premium Lager Dog"
   .
   .
   .

预期输出:迈克

Answer 1

我希望这有用，如果不是(或者如果我拼写错误)，请不要投票否决(别名可能需要 AS 关键字，取决于数据库类型)

select count(*), drinker from (
select  a.drinker, b.beer 
    FROM BarBeerDrinker.likes a , BarBeerDrinker.likes b
     where a.beer = b.beer and b.bar = 'Caravan'
   group by a.drinker, b.beer )
group by drinker
   having count(*) = ( select count(*) from BarBeerDrinker.likes c where c.bar = 'Caravan' )