Mysql 选择在一列中具有相同值而在另一列中具有不同值的行

Question

如果有人可以验证我的 SQL 查询，我将不胜感激。

对于以下数据集:

MD5      UserPK      CategoryPK    
ADCDE    1           7  
ADCDE    1           4  
ADCDE    1           7  
dffrf    1           7  
dffrf    2           7  
dffrf    2           6 
dffrf    1           1 

我想选择 MD5 和 CategoryPK，其中两行或多行具有相同的 MD5 值、相同的 CatgegoryPK 和两个或更多不同的 UserPK 值。

换句话说，我想知道两个或多个不同用户 (UserPK) 已将相同类别 (UserPK) 分配给同一文件 (Md5) 的所有记录的 MD5 和类别 PK。我对同一用户已多次分配类别的记录不感兴趣(除非不同的用户也已将同一类别分配给该文件)。

所以根据上面的数据，我想返回:

md5    CategoryPK
dffrf  7

我写的查询是:

SELECT md5, 
       count(md5), 
       count(distinct categorypk) as cntcat, 
       count(distinct userpk) as cntpk
FROM Hash
       group by md5 having count(md5) > 1 
                           and cntpk > 1
                           and cntcat = 1;

它似乎有效，但在我愤怒地开始使用它之前，如果我遗漏了什么或者是否有更好的方法，我会很感激第二意见。

谢谢

Answer 1

我认为您的代码不会给您想要的东西；当一个文件被多个用户分配了多个类别并且某些类别重叠时会发生什么？然后 cntcat != 1，因此您的 HAVING 子句将无法匹配，即使该文件确实已被多个用户以相同的方式分类。

我会改为使用自连接:

SELECT   a.MD5, a.CategoryPK
FROM     Hash a
  JOIN   Hash b
      ON a.MD5 = b.MD5
     AND a.UserPK <> b.UserPK
     AND a.CategoryPK = b.CategoryPK
GROUP BY a.MD5, a.CategoryPK
HAVING   COUNT(DISTINCT a.UserPK) > 2  -- you said "more than 2" ?