我有一个表与以下字段:
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查询。
当前回答
这个解决方案将把重复的数据移到一个表中，唯一的数据移到另一个表中。
-- speed up creating uniques table if dealing with many rows
CREATE INDEX temp_idx ON jobs(site_id, company, title, location);
-- create the table with unique rows
INSERT jobs_uniques SELECT * FROM
(
SELECT *
FROM jobs
GROUP BY site_id, company, title, location
HAVING count(1) > 1
UNION
SELECT *
FROM jobs
GROUP BY site_id, company, title, location
HAVING count(1) = 1
) x
-- create the table with duplicate rows
INSERT jobs_dupes
SELECT *
FROM jobs
WHERE id NOT IN
(SELECT id FROM jobs_uniques)
-- confirm the difference between uniques and dupes tables
SELECT COUNT(1)
AS jobs,
(SELECT COUNT(1) FROM jobs_dupes) + (SELECT COUNT(1) FROM jobs_uniques)
AS sum
FROM jobs
其他回答
如果您试图删除其中一个副本并保留另一个副本，那么这是完美的。注意，如果没有子查询，您将得到#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
)
你可以很容易地从这个代码中删除重复的记录。
$qry = mysql_query("SELECT * from cities");
while($qry_row = mysql_fetch_array($qry))
{
$qry2 = mysql_query("SELECT * from cities2 where city = '".$qry_row['city']."'");
if(mysql_num_rows($qry2) > 1){
while($row = mysql_fetch_array($qry2)){
$city_arry[] = $row;
}
$total = sizeof($city_arry) - 1;
for($i=1; $i<=$total; $i++){
mysql_query( "delete from cities2 where town_id = '".$city_arry[$i][0]."'");
}
}
//exit;
}
简单和快速的所有情况:
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_duplicates AS (SELECT dub.id FROM table_with_duplications dub GROUP BY dub.field_must_be_uniq_1, dub.field_must_be_uniq_2 HAVING COUNT(*) > 1);
DELETE FROM table_with_duplications WHERE id IN (SELECT id FROM _temp_duplicates);
下面是我用过的方法，它很有效:
create table temp_table like my_table;
T_id是唯一的列
insert into temp_table (id) select id from my_table GROUP by t_id;
delete from my_table where id not in (select id from temp_table);
drop table temp_table;
删除表中重复的记录。
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);
