Mysql选择一列中具有相同值，另一列具有不同值的行 [英] Mysql Select rows with same values in one column and different in another

问题描述

如果有人可以验证我的SQL查询，我会非常感谢。

I'd really appreciate it if someone could validate my SQL query.

对于以下数据集：

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值的两行或多行。

I'd like to select MD5 and CategoryPK where two or more rows exist with identical MD5 values, identical CatgegoryPK and two or more DIFFERENT UserPK values.

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

In other words, I'd like to know the MD5 and categoryPK of all records where two or more different users (UserPK) have assigned the same category (UserPK) to the same file (Md5). I'm not interested in records the same user has assigned the category to multiple times, (unless a different user has also assigned the same category to that file).

从上面的数据，我想只返回：

So from the above data, I would like to be returned just:

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;

似乎工作，但在我开始使用它的愤怒，

It seems to work, but before I start using it in anger, I'd appreciate a second opinion in case I've missed something or if there is a better way of doing it.

感谢

推荐答案

我不认为你的代码会给你你的后;当一个文件被多个用户分配到多个类别时，会发生什么，一些类别重叠？然后 cntcat！= 1 ，因此您的 HAVING 子句将无法匹配，即使文件确实已分类

I don't think your code will give you what you're after; what happens when a file has been assigned more than one category by multiple users, with some categories overlapping? Then cntcat != 1, so your HAVING clause will fail to match even though the file has indeed been categorised the same way by multiple users.

我会改用自联接：

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

这篇关于Mysql选择一列中具有相同值，另一列具有不同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！