MYSQL删除一个字段相同记录，保留ID最小记录

Question

test1
id  c_id
1   0013
2   0014
3   0013
4   0013我只是查询就没反应了，表的记录在5千以上。我用的查询命令是：
select * from test1 where c_id in (
select id from test1 
group by c_id 
having count(c_id) > 1
)怎么把相同c_id的记录删除掉，保留id最小的一个，最好是效率高的语句。如果是多个字段相同呢，语句又该怎么写？

Answer 1

delete from test1 a
where a.c_id in  
(select c_id from test1 group by c_id having count(*) > 1)
and a.id not in (select min(id) from test1 group by c_id having count(*)>1)下面这个是有多个字段相同的例子，方便你以后遇到时可以解决：
delete from vitae a
where (a.peopleId,a.seq) in  
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

Answer 2

delete from test1 a where a.id not in (select min(id) from test1 group by c_id);/*单字段的重复合并*/
delete from test1 a where a.id not in (select min(id) from test1 group by field1,field2);/*多字段的重复合并*/

Answer 3

忘了，加上索引效率应该高些：delete from test1 a where a.id not in (select min(id) from test1 order by c_id group by c_id);/*单字段的重复合并*/
delete from test1 a where a.id not in (select min(id) from test1 order by field1,field2 group by field1,field2);/*多字段的重复合并*/

Answer 4

不好意思，上面的排序错误，应该是：delete from test1 a where a.id not in (select min(id) from （select * from test1 order by c_id）as tb group by c_id);/*单字段的重复合并*/
delete from test1 a where a.id not in (select min(id) from （select * from test1 order by field1,field2）as tb group by field1,field2);/*多字段的重复合并*/

Answer 5

mysql> select * from test1;
+------+------+
| id   | c_id |
+------+------+
|    1 | 0013 |
|    2 | 0014 |
|    3 | 0013 |
|    4 | 0013 |
+------+------+
4 rows in set (0.06 sec)
mysql> delete t from test1 t left join
    ->  (select c_id,min(id) as min_id from test1 group by c_id) t1
    ->  on t.id=t1.min_id
    -> where t1.min_id is null;
Query OK, 2 rows affected (0.06 sec)mysql> select * from test1;
+------+------+
| id   | c_id |
+------+------+
|    1 | 0013 |
|    2 | 0014 |
+------+------+
2 rows in set (0.00 sec)mysql>

Answer 6

如果记录不多的话（大于10万），可以利用主键来解决重复的问题；即是先建立和原来数据表一摸一样的新表，但是在要在有重复的字段上建立主键，然后把原来的表导入到这个新表中，利用ignore关键字来忽略重复。insert ignore into new_table select * from old_table；我基本上都是用这种方法，速度比较快，操作很简单

Answer 7

谢谢Jasun_Li  daniel75122你们的语句效率不高，有in的似乎都不高，执行了2个小时都还没有出结果。ACMAIN_CHM 的方法不错，谢谢了。

Answer 8

delete t from test1 t left join (select c_id,min(id) as min_id from test1 group by c_id) t1 on t.id=t1.min_id where t1.min_id is null;
这句里面t,t1是什么意思？