MySQL查询查找与另一行具有相同值的所有行 [英] Mysql query to find all rows that have the same values as another row

问题描述

我的数据库包含的行通常如下所示:

My database contains rows that generally look like:

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列与数据库中针对这两列的其他记录相匹配....

I need to find all the actual records where the PersonId and the ItemId column match some other record in the database for those two columns....

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

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

如何获得这些结果?

推荐答案

您可以进行联接以处理重复的记录.

You can do joins to get around with duplicate records.

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演示
• SQLFiddle Demo

输出

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

这篇关于MySQL查询查找与另一行具有相同值的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！