MySQL根据另一个列值查找重复项 [英] MySQL find duplicates based on another column value

问题描述

我有下表

+----+------+-------+
| id | user | value |
+----+------+-------+
|  1 |  10  |   A   |
|  2 |  12  |   B   |
|  3 |  24  |   A   |
|  4 |  33  |   C   |
+----+------+-------+

我要检索具有相同密钥的所有重复用户

I want to retreive all the duplicates users that have the same key

+----+------+-------+
| id | user | value |
+----+------+-------+
|  1 |  10  |   A   |
|  3 |  24  |   A   |
+----+------+-------+

我尝试过没有运气

SELECT DISTINCT A.user, A.value
FROM table as A
INNER JOIN ( SELECT value FROM table GROUP BY value HAVING COUNT(value) > 1 ) AS B
ON A.value = B.value 

推荐答案

您可以在下面的查询中尝试-

You may try below query -

SELECT id, user, value
FROM YUOR_TABLE T1
WHERE EXISTS (SELECT 1
              FROM YOUR_TABLE T2
              WHERE T1.value = T2.value
              AND T1.user <> T2.user)

这篇关于MySQL根据另一个列值查找重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！