php mysql统计去掉重复的,php - Mysql：根据最小数量删除重复记录 - 堆栈内存溢出...

我需要根据相同order number最小amount删除重复记录。 表有10万条记录，结构是这样的。 tmp_id是此表中的primary key 。

------------------------------------

user_id order_number amount tmp_id

-------------------------------------

15 12364 25 1

20 454544 75 2

4 12364 100 3

6 45487 45 4

8 454544 330 5

我试图这样delete的最小重复记录数量

$qb_duplicate_data_query="select user_id,order_number,amount from tmp_qbappraiser limit 10";

$qb_duplicate_data_sql=mysql_query($qb_duplicate_data_query) or die(mysql_error());

while($row=mysql_fetch_array($qb_duplicate_data_sql)) {

$amount=$row['amount'];

$order_no=$row['order_number'];

$sql="SELECT c1.tmp_id from tmp_qbappraiser c1

inner join (SELECT tmp_id FROM tmp_qbappraiser WHERE order_number='$order_no' ORDER BY amount asc) AS c

on c.tmp_id=c1.tmp_id GROUP BY c1.order_number having count(order_number)>1";

$query=mysql_query($sql) or die(mysql_error());

$result=mysql_fetch_array($query);

$db_tmp_id=$result['tmp_id'];

if($db_tmp_id!='') {

$delete_sql="delete from tmp_qbappraiser where order_number='$order_no' and tmp_id='$db_tmp_id'";

$delete_query=mysql_query($delete_sql) or die(mysql_error());

}

}

当我执行此代码时，我得到了lock wait timeout exceeded try restarting transaction错误。 如何优化我的查询，或者是否有其他方法可以删除数千条记录？