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和categoryPK。我对同一用户多次分配该类别的记录不感兴趣(除非另一个用户也为该文件分配了相同的类别)。

因此，从上面的数据中，我想返回以下内容：

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" ?

Answer 2

我看不出你写的东西有什么问题，除了你没有得到你的选择列表中的类别，这似乎在标准中？我认为你可以稍微简化一下，去掉这个类别：

SELECT  MD5, CategoryPK
FROM    Hash
GROUP BY MD5, CategoryPK
HAVING MIN(UserPK) <> MAX(UserPK)

或者，您可以考虑使用join来解决此问题，您可能需要运行一些测试并使用EXPLAIN，但有时join的性能比GROUP BY更好。无论如何，还是值得一试，看看你是否看到了任何显着的差异。

SELECT  DISTINCT t1.MDF, t2.CategoryPK
FROM    Hash T1
        INNER JOIN Hash T2
            ON T1.MD5 = T2.MD5
            AND T1.CategoryPK = T2.CategoryPK
            AND T1.UserPK < T2.UserPK