可能重复:
如何使用SQL删除重复行?
我有一个记录表,想要删除所有重复的记录。
DELETE FROM 'table'
WHERE 'field' IN
(
SELECT 'field' FROM 'table' GROUP BY 'field'
HAVING (COUNT('field')>1)
)
为什么这个不起作用?
可能重复:
如何使用SQL删除重复行?
我有一个记录表,想要删除所有重复的记录。
DELETE FROM 'table'
WHERE 'field' IN
(
SELECT 'field' FROM 'table' GROUP BY 'field'
HAVING (COUNT('field')>1)
)
DISTINCT
来选择基于某个字段的唯一记录。CREATE TABLE nonDuplicates
SELECT DISTINCT * FROM yourTable group by field
SELECT field FROM `table`
GROUP BY field
HAVING (COUNT('field)>1
SELECT TOP 1 field
FROM `table`
GROUP BY field
HAVING (COUNT(field)>1
首先获取所有重复项并将它们存储在数组中。
SELECT field
FROM `table`
GROUP BY field
HAVING COUNT('field') > 1
现在你可以编写PHP代码,保存任何你想要的结果并执行它。
DELETE
FROM `table`
WHERE field IN (your values) AND field != savedID
id
的列):DELETE d
FROM table_with_duplicates d
JOIN (
SELECT min(id) as min_id, field
FROM table_with_duplicates
GROUP BY field
) keep ON keep.field = d.field
AND keep.min_id <> d.id;
这将保留每个重复记录的一行(其中id
列中具有最低值的行)。
如果您想要删除所有重复行(而不是至少保留一行),只需删除AND keep.min_id <> d.id
条件即可。
编辑
如果您没有唯一的列,但想要删除所有重复项(不保留至少一行记录),则可以使用如下:
DELETE d
FROM table_with_duplicates d
JOIN (
SELECT field
FROM table_with_duplicates
GROUP BY field
HAVING count(*) > 1
) del ON del.field = d.field;