如何查找多列匹配的重复项 [英] How to find duplicates where multiple columns match
问题描述
我想在 X、Y 和 Z 匹配的表中找到重复项，以最终清除由时间戳标识的旧项.
I'd like to find dublicates in a table where X, Y, and Z matches, to finally clean the old one which is identified by a timestamp.
+------------+-----+----+-----+
| Created | X | Y | Z |
+------------+-----+----+-----+
| 1515622543 | 334 | 72 | 269 |
| 1515622544 | 334 | 72 | 270 |
| 1515622601 | 334 | 72 | 268 |
| 1515622953 | 334 | 72 | 268 |
+------------+-----+----+-----+
在此示例中，X=334, Y=72, Z=268 处有一个重复项.我想列出它们 - 所以它最终看起来像:
In this example there is an dublicate at X=334, Y=72, Z=268. I would like to list them - so it finally looks like:
+------------+-----+----+-----+
| 1515622601 | 334 | 72 | 268 |
| 1515622953 | 334 | 72 | 268 |
+------------+-----+----+-----+
已经尝试过:
从decayworld中选择count(distinct X)、count(distinct Y)、count(distinct Z)； - 只是计数，不显示所有 3 (X,Y,Z) 匹配的位置.
select count(distinct X), count(distinct Y), count(distinct Z) from decayworld;
- which just counts and do not show me where ALL 3 (X,Y,Z) matches.
SELECT X, Y, Z, COUNT(*) FROM Decayworld GROUP BY X, Y, Z HAVING COUNT(*) >1;+-----+----+-----+------------+|X |是 |Z |COUNT(*) |+-----+----+-----+------------+|第334话72 |第268话2 |+-----+----+-----+------------+ - 它计算结果，但不列出它们.
SELECT X, Y, Z, COUNT(*) FROM decayworld GROUP BY X, Y, Z HAVING COUNT(*) > 1;
+-----+----+-----+----------+
| X | Y | Z | COUNT(*) |
+-----+----+-----+----------+
| 334 | 72 | 268 | 2 |
+-----+----+-----+----------+
- it counts the results, but not list them.
推荐答案
鉴于 OP 想要删除所有较旧的记录(这是在提出原始问题后的评论中规定的)，这个查询给出了那个结果.此解决方案假设 id 列是唯一的，并按从旧到新的升序排列:
Given that the OP wants to delete all of the older records (which was stipulated in the comments after the original question was asked), this query gives that outcome. This solution is assuming that the column id is unique and sorted ascending from oldest to newest:
DELETE FROM mytable WHERE NOT EXISTS (
SELECT * FROM (
SELECT MAX(id) AS id FROM mytable GROUP BY x, y, z
) AS keepers
WHERE keepers.id = mytable.id
);
这篇关于如何查找多列匹配的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
