MySQL查询以查找与另一行具有相同值的所有行。

Question

MySQL查询以查找与另一行具有相同值的所有行。

5

我的数据库通常包含以下格式的行：

PersonItem
__________
id
personId
itemId

╔════╦══════════╦════════╗
║ ID ║ PERSONID ║ ITEMID ║
╠════╬══════════╬════════╣
║  1 ║      123 ║    456 ║
║  2 ║      123 ║    456 ║
║  3 ║      123 ║    555 ║
║  4 ║      444 ║    456 ║
║  5 ║      123 ║    456 ║
║  6 ║      333 ║    555 ║
║  7 ║      444 ║    456 ║
╚════╩══════════╩════════╝

我需要查找所有实际记录，其中PersonId和ItemId列匹配数据库中其他记录的这两列....

| 1  |   123    |   456
| 2  |   123    |   456
| 5  |   123    |   456

| 4  |   444    |   456
| 7  |   444    |   456

我该如何获得这些结果？

- kasdega

你的意思是两个都要匹配，还是至少有一个匹配？ - Eugen Rieck

4个回答

4

类似这样的代码应该可以解决问题：

SELECT P1.*
FROM PersonItem P1
INNER JOIN PersonItem P2 ON P2.ID <> P1.ID
AND P2.PersonId = P1.PersonId
AND P2.ItemId =   P1.ItemId

- Chief Wiggum

这是最简洁的答案。 - Urbycoz

3

您需要找到出现过一次以上的personid/itemid示例。在MySQL中，您可以使用where子句和子查询来执行此操作：

select t.*
from t
where exists (select 1
              from t t2
              group by personid, itemid
              having count(*) > 1 and
                     t2.personid = t.personid and t2.itemid = t.itemid
             )

上述是标准SQL。MySQL还支持多列in语句。因此，可以写成：

select t.*
from t
where (t.personid, t.itemid) in (select personid, itemid
                                 from t
                                 group by personid, itemid
                                 having count(*) > 1
                                )

我喜欢的一种替代方法，基于 Eugene 的答案但更有效率，是：

SELECT t.personid, t.ItemId, GROUP_CONCAT(t.ID)
FROM t
GROUP BY t.personid, t.ItemId
HAVING COUNT(*) > 1;

它可以消除任何连接，如果您不介意将ID作为列表而不是单独的行获取。

- Gordon Linoff

@EugenRieck . . . 正如你所说，这是有系统的。但是，“每一行只执行一次”将成为一个索引查找，配合适当的索引使用，可以使其在性能上等同于连接操作。 - Gordon Linoff

关于您对我的答案的变化：我并不认为这更有效率：它将创建一个包含所有组的临时表，因为HAVING只能在第一次循环结束后应用。连接将仅接受具有至少一个重复项的行。如果您有许多行但很少重复项，则保证HAVING非常低效。 - Eugen Rieck

@EugenRieck . . . 这很有趣。我没有意识到执行计划实际上会被重新创建。随着5.6中的其他优化一起，这方面有所改进吗？ - Gordon Linoff

@GordonLinoff 我们所有生产环境的MySQL都出现了这个问题，所以至少在5.5版本后期就已经存在了。不过这确实有道理！ - Eugen Rieck

@GordonLinoff 喜欢这个修订（第二个答案）!! - kasdega

显示剩余4条评论

3

SELECT GROUP_CONCAT(p1.ID), p1.personid, p1.ItemId
FROM PersonItem AS p1
INNER JOIN PersonItem AS p2  ON 
    p1.ID<>p2.ID
    AND p1.personid=p2.personid
    AND p1.ItemId=p2.ItemId
GROUP BY p1.personid, p1.ItemId

- Eugen Rieck

@GordonLinoff 为什么要这样做呢？这是一个INNER JOIN，我已经通过p1.ID<>p2.ID排除了自连接。 - Eugen Rieck

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Woo · Accepted Answer

您可以使用联接来解决重复记录的问题。

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  PersonID, ItemID, COUNT(*) totalCount
            FROM    TableName
            GROUP   BY PersonID, ItemID
            HAVING  COUNT(*) > 1
        ) b ON  a.PersonID = b.PersonID AND
                a.ItemID = b.ItemID

SQLFiddle演示

输出结果

╔════╦══════════╦════════╗
║ ID ║ PERSONID ║ ITEMID ║
╠════╬══════════╬════════╣
║  1 ║      123 ║    456 ║
║  2 ║      123 ║    456 ║
║  5 ║      123 ║    456 ║
║  4 ║      444 ║    456 ║
║  7 ║      444 ║    456 ║
╚════╩══════════╩════════╝