mysql获取相异行,MySQL查询查找与另一行具有相同值的所有行

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 ║

╚════╩══════════╩════════╝

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

How can I go about getting these results?

解决方案

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

OUTPUT

╔════╦══════════╦════════╗

║ ID ║ PERSONID ║ ITEMID ║

╠════╬══════════╬════════╣

║ 1 ║ 123 ║ 456 ║

║ 2 ║ 123 ║ 456 ║

║ 5 ║ 123 ║ 456 ║

║ 4 ║ 444 ║ 456 ║

║ 7 ║ 444 ║ 456 ║

╚════╩══════════╩════════╝