MySQL 检查一对多关系中的重复条目

Question

我有两个处于一对多关系的表。我想检查映射表中是否有重复的条目。

客户:

+----------------------------+--------------+------+-----+---------+----------------+
| Field                      | Type         | Null | Key | Default | Extra          |
+----------------------------+--------------+------+-----+---------+----------------+
| uid                        | int(11)      | NO   | PRI |         | auto_increment |
| active                     | tinyint(1)   | NO   |     |         |                |
| locked                     | tinyint(1)   | NO   |     |         |                |
+----------------------------+--------------+------+-----+---------+----------------+

项目映射:

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| uid          | int(11)       | NO   | PRI |         | auto_increment |
| customer_uid | int(11)       | NO   |     |         |                |
| device_uid   | int(11)       | NO   |     |         |                |
| offered      | float(11,2)   | NO   |     |         |                |
+--------------+---------------+------+-----+---------+----------------+

如何检查表 items_mapping 中是否有任何客户具有重复的 device_uid？

简而言之，这会起作用:

+-------+--------------+------------+---------+
| uid   | customer_uid | device_uid | offered |
+-------+--------------+------------+---------+
|  1132 |   53442      |      24    |  2.00   |
|  8242 |   53442      |      45    |  4.00   |
| 15122 |   53442      |      12    |  5.00   |
+-------+--------------+------------+---------+

但不应该是:

+-------+--------------+------------+---------+
| uid   | customer_uid | device_uid | offered |
+-------+--------------+------------+---------+
|  1132 |   53442      |      24    |  2.00   |
|  8242 |   53442      |      45    |  4.00   |
| 15122 |   53442      |      24    |  2.00   |
+-------+--------------+------------+---------+

Answer 1

只需将 customer_uid 和 device_uid 与 having 子句一起分组即可:

SELECT
    customer_uid,
    device_uid,
    COUNT(device_uid) count_device_uid
FROM
    items_mapping
GROUP BY
    customer_uid,
    device_uid
HAVING
    COUNT(device_uid) > 1

这将为您提供具有重复 device_uid 的所有客户