我有一个表与以下字段:
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查询。
当前回答
下面是我用过的方法，它很有效:
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;
其他回答
这个解决方案将把重复的数据移到一个表中，唯一的数据移到另一个表中。
-- 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
还有另一种解决方案:
DELETE t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ...
如果你有一个有大量记录的大表，那么上述解决方案将不起作用或花费太多时间。然后我们有一个不同的解
-- Create temporary table
CREATE TABLE temp_table LIKE table1;
-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);
-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;
-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;
你可以很容易地从这个代码中删除重复的记录。
$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;
}
我有这个查询片段的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)
