sql删除重复数据

一:创建表

create table userinfo

(

　　id int,

　　name varchar(10),

　　age int,

　　sex int,

　　address varcahr(50)

)

二:添加数据

insert into userinfo values(1,'admin','20,'1','上海市');

insert into userinfo values(2,'admin','20,'1','上海市');

insert into userinfo values(3,'admin','20,'1','上海市');

insert into userinfo values(4,'admin','20,'1','上海市');

insert into userinfo values(4,'admin','20,'1','上海市');

commit;

三:查询重复的数据

select id, name,age,sex,address from userinfo where (name,age,sex,address) in (select name,age,sex,address from userinfo group by name,age,sex,address having count(1) > 1);

四:保留最小id的一行数据

select id, name,age,sex,address from userinfo where (name,age,sex,address) in (select name,age,sex,address from userinfo group by name,age,sex,address having count(1) > 1) and id not in(select min(id) from userinfo group by name,age,sex,address having count(1) >1);

五:删除重复数据

delete from usrinfo where id in (select id from userinfo where (name,age,sex,address) in (select name,age,sex,address from userinfo group by name,age,sex,address having count(1) > 1) and id not in(select min(id) from userinfo group by name,age,sex,address having count(1) >1));

commit;

注: 如果没有id,可以用oracle中的ROWNUM来添加虚拟的id;