mysql - 如何比较sql中同一个表的行

Question

我的 table 是:

frequents(bar,drinker);
likes(beer,drinker);
serves(bar,beer)

我想“选择经常光顾同一酒吧的成对饮酒者”。我想我可以仅使用频繁表(因为它有酒吧和饮酒者列)使用我尝试做但无法做到的自连接来编写该查询不明白。我不介意使用其他表来获得准确的查询。查询必须选择只去同一酒吧的饮酒者。换句话说，他们应该拥有所有公共(public)酒吧。查询必须是以广义形式，它不应该依赖于数据，这就是我没有放置任何数据的原因。

DRINKER |     BAR  
____________________    
John    |     Hyatt       
Smith   | Blue     
William | Hilton   
John    | Geoffreys 
Smith   | Hyatt     
Joe     | Blue      
Mike    | Hilton    
William | Dublin        
Jeff    | Hilton        
Jake    | Hilton    

这是我的常客表，我只需要选择 Joe 和 Smith，还有 Jake 和 Jeff，因为他们访问完全相同的酒吧。

Answer 1

MySQL 中最简单的方法是使用 group_concat() 将值放在一起并进行比较:

select fd.bars, group_concat(fd.drinker) as drinkers
from (select f.drinker, group_concat(f.bar order by f.bar) as bars
      from frequents f
      group by f.drinker
     ) fd
group by bars
having count(*) > 1;

编辑

您也可以使用join来完成此操作，但要正确执行此操作，您需要一个完全外部联接——MySQL 不支持该功能。

另一种方法是计算每个柱的数量，进行内部连接，并确保计数与柱相匹配:

select f1.drinker, f2.drinker
from frequents f1 join
     frequents f2
     on f1.bar = f2.bar join
     (select f.drinker, count(*) as numbars
      from frequents f
      group by f.drinker
     ) fd1
     on f1.drinker = fd1.numbars join
     (select f.drinker, count(*) as numbars
      from frequents f
      group by f.drinker
     ) fd2
     on f2.drinker = f2.drinker
group by f1.drinker, f2.drinker
having count(*) = max(f1.numbars) and count(*) = max(f2.numbars);