个人感觉不错的SQL语句

删除重复行

delete from t6 where rowid not in (     
select max(t6.rowid) from  t6 group by      
t6.bm,t6.mc);  
--这里用min(rowid)也可以。

具体可详看http://elf8848.iteye.com/blog/761471博客

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
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)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * 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)

mysql中不能这么用。 （等待mysql升级吧）
错误提示就是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)

替换方案：
create table tmp as select min(id) as col1 from blur_article group by title;
delete from blur_article where id not in (select col1 from tmp);
drop table tmp;