Question

MySql删除重复记录问题

表emp中有主键empid，现在我想要删除非主键字段empno重复的记录，高手指点一下，写个具体的语句，从别的问题那COPY来的就别发了

Answer 1

mysql的删除动作不能带有本表的查询动作，意思是你删除A表的东西不能以A表的信息为条件，如下不可以：
delete from A where aa in (select bb from A);
你可以通过建另外一张表，两张表匹配关系操作，如：
create table t_b as select * from t_a group by empno;
delete from t_a where empid not exists (select 1 from t_b where t_a.empid=t_b.empid);

Answer 2

insert into tmp (min_empid,num,empids,empno)
select min(empid) min_empid,count(*) num,group_concat(empid) empids ,empno
from emp
group by empno
having num > 1 ;
delete from emp where empid not in (select min_empid from tmp) and find_in_set(empid,(select empids from tmp where tmp.empno=emp.empno)

Answer 3

以empno相同的，保留empid最小的记录
delete a from [emp] a where exists(select 1 from [emp] where [empno]=a.[empno] and empid<a.empid)