日期:2014-05-18  浏览次数:20494 次

删除重复项的问题
要求是只要有一列重复,就把重复的两条数据全部删除,一条也不留
比如:一个表中的moblie项,有两条数据的moblie项数据相同,则把这两条数据全部删掉。


------解决方案--------------------
delete from tb where moblie in (select moblie from tb group by moblie having count(*) > 1)
------解决方案--------------------
delete from 表名 where moblie in (select moblie from 表名 group by moblie having count(moblie)>1)
------解决方案--------------------
create table summersnow_182
(id varchar(5), moblie int)

insert summersnow_182
select '01', 2
union all select '02', 4
union all select '03', 7
union all select '04', 7
union all select '05', 2
union all select '06', 3
union all select '07', 4
union all select '08', 11

select * from summersnow_182


delete summersnow_182
 from summersnow_182 B,
(select moblie ,count(1) Cnt from summersnow_182 group by moblie)A 
where A.Cnt >=2 and A.moblie =B.moblie


------------------------------------
id moblie 
06 3
08 11