-- 查找重复条目
SELECT ID,
COUNT(ID)
FROM IDrecord
GROUP BY ID
HAVING COUNT(ID) > 1
-- 删除重复条目
DELETE FROM IDrecord WHERE SerialNo NOT IN (
SELECT t.max_id from (
SELECT MAX(SerialNo) as max_id from IDrecord GROUP BY ID
) as t
)
假设IDrecord表如下:
serialNo | ID |
---|---|
1 | xxx |
2 | ccc |
3 | aaa |
4 | ccc |
5 | xxx |
6 | xxx |
7 | aaa |
8 | zzz |
则查找重复条目的搜索结果应为:
ID | COUNT |
---|---|
xxx | 3 |
ccc | 2 |
aaa | 2 |
zzz | 1 |
删除重复条目后的结果为:
serialNo | ID |
---|---|
1 | xxx |
2 | ccc |
3 | aaa |
8 | zzz |