SQL直接删除冗余数据
/**
*删除表中有多行的重复的数据的多余记录，即只保留一行，多余的全删除
*/
//表模型：
表名 --tb
－－－－－－－－－－
｜ id｜ n ｜ v ｜
－－－－－－－－－
｜ 1 ｜ a ｜ 3 ｜
－－－－－－－－－
｜ 2 ｜ a ｜ 1 ｜
－－－－－－－－－
｜ 3 ｜ a ｜ 1 ｜
－－－－－－－－－
｜ 4 ｜ b ｜ 2 ｜
－－－－－－－－－
｜ 5 ｜ b ｜ 2 ｜
－－－－－－－－－
｜ 6 ｜ c ｜ 3 ｜
－－－－－－－－－
｜ 7 ｜ d ｜ 1 ｜
－－－－－－－－－－
处理后：
－－－－－－－－－－
｜ id｜ n ｜ v ｜
－－－－－－－－－
｜ 1 ｜ a ｜ 3 ｜
－－－－－－－－－
｜ 2 ｜ a ｜ 1 ｜
－－－－－－－－－
｜ 4 ｜ b ｜ 2 ｜
－－－－－－－－－
｜ 6 ｜ c ｜ 3 ｜
－－－－－－－－－
｜ 7 ｜ d ｜ 1 ｜
－－－－－－－－－－
//--四种代表方式如下，其中同一条语句中的rowid和id可以互相全部取代；max(..)与min(..)效果一致--//
1.> delete from tb a where rowid not in
(select max(b.rowid) from tb b where a.n=b.n and a.v=b.v);
或
delete from tb a where rowid not in
(select max(b.id) from tb b where a.n=b.n and a.v=b.v);
2.> delete from tb where rowid in
(select a.rowid form tb a,tb b where a.rowid>b.rowid
and a.n=b.n and a.v=b.v);
3.> delete from tb where rowid not in
(select max(rowid) from tb t group by t.n,t.v);
或
delete from tb where rowid not in
(select min(id) from tb t group by t.n,t.v);
4.> delete from tb where (n,v) in
(select n,v from tb group by n,v
having count(*)>1)
and rowid not in
(select min(rowid) from tb group by n,v
having count(*)>1);
