我有一个表与以下字段:
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查询。
当前回答
你可以很容易地从这个代码中删除重复的记录。
$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;
}
其他回答
如果不想更改列属性，那么可以使用下面的查询。
因为你有一个列，它有唯一的id(例如，auto_increment列)，你可以用它来删除重复项:
DELETE `a`
FROM
`jobs` AS `a`,
`jobs` AS `b`
WHERE
-- IMPORTANT: Ensures one version remains
-- Change "ID" to your unique column's name
`a`.`ID` < `b`.`ID`
-- Any duplicates you want to check for
AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);
在MySQL中，你可以用空安全的相等操作符(又名“宇宙飞船操作符”)来简化它:
DELETE `a`
FROM
`jobs` AS `a`,
`jobs` AS `b`
WHERE
-- IMPORTANT: Ensures one version remains
-- Change "ID" to your unique column's name
`a`.`ID` < `b`.`ID`
-- Any duplicates you want to check for
AND `a`.`title` <=> `b`.`title`
AND `a`.`company` <=> `b`.`company`
AND `a`.`site_id` <=> `b`.`site_id`;
你可以很容易地从这个代码中删除重复的记录。
$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;
}
使用Delete JOIN语句删除重复的行 MySQL为您提供了DELETE JOIN语句，您可以使用它快速删除重复的行。
下面的语句删除重复的行并保留最高的id:
DELETE t1 FROM contacts t1
INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;
还有另一种解决方案:
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 ...
我想更具体地说明我删除了哪些记录，下面是我的解决方案:
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
)
