我有一个表与以下字段:
id (Unique)
url (Unique)
title
company
site_id
现在，我需要删除具有相同标题、company和site_id的行。一种方法是使用下面的SQL和脚本(PHP):
SELECT title, site_id, location, id, count( * )
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1
运行此查询后，可以使用服务器端脚本删除重复项。
但是，我想知道这是否只能使用SQL查询。
当前回答
删除表中重复的记录。
delete from job s
where rowid < any
(select rowid from job k
where s.site_id = k.site_id and
s.title = k.title and
s.company = k.company);
or
delete from job s
where rowid not in
(select max(rowid) from job k
where s.site_id = k.site_id and
s.title = k.title and
s.company = k.company);
其他回答
我必须对文本字段执行此操作，并且遇到了索引上100字节的限制。
我通过添加一个列来解决这个问题，对字段进行md5哈希，并进行更改。
ALTER TABLE table ADD `merged` VARCHAR( 40 ) NOT NULL ;
UPDATE TABLE SET merged` = MD5(CONCAT(`col1`, `col2`, `col3`))
ALTER IGNORE TABLE table ADD UNIQUE INDEX idx_name (`merged`);
我有这个查询片段的SQLServer，但我认为它可以用在其他DBMS与小的变化:
DELETE
FROM Table
WHERE Table.idTable IN (
SELECT MAX(idTable)
FROM idTable
GROUP BY field1, field2, field3
HAVING COUNT(*) > 1)
我忘了告诉您，这个查询不会删除重复行中id最低的行。如果这对你有用，试试这个查询:
DELETE
FROM jobs
WHERE jobs.id IN (
SELECT MAX(id)
FROM jobs
GROUP BY site_id, company, title, location
HAVING COUNT(*) > 1)
MySQL对引用要删除的表有限制。你可以用一个临时表来解决这个问题，比如:
create temporary table tmpTable (id int);
insert into tmpTable
(id)
select id
from YourTable yt
where exists
(
select *
from YourTabe yt2
where yt2.title = yt.title
and yt2.company = yt.company
and yt2.site_id = yt.site_id
and yt2.id > yt.id
);
delete
from YourTable
where ID in (select id from tmpTable);
以下是Kostanos在评论中的建议: 上面唯一缓慢的查询是DELETE，适用于数据库非常大的情况。这个查询可以更快:
DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id
我想更具体地说明我删除了哪些记录，下面是我的解决方案:
delete
from jobs c1
where not c1.location = 'Paris'
and c1.site_id > 64218
and exists
(
select * from jobs c2
where c2.site_id = c1.site_id
and c2.company = c1.company
and c2.location = c1.location
and c2.title = c1.title
and c2.site_id > 63412
and c2.site_id < 64219
)
如果您试图删除其中一个副本并保留另一个副本，那么这是完美的。注意，如果没有子查询，您将得到#1093错误。
DELETE FROM table_name
WHERE id IN (
SELECT * FROM (SELECT n.id FROM table_name n
WHERE n.column2 != "value"
GROUP BY n.column HAVING COUNT(n.column ) > 1) x
)
