SQL删除重复数据只保留一条

最新推荐文章于 2024-07-11 10:27:20 发布

shiml1991

最新推荐文章于 2024-07-11 10:27:20 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： sql 重复删除

本文链接：https://blog.csdn.net/zoro_13/article/details/16975301

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

--创建表

create table student(sid varchar2(4) primary key,
sname varchar2(10) not null,
sex char(6) check(sex in('男','女')))

--创建序列

create sequence seq_s
start with 1
minvalue 1
increment by 1
maxvalue 999;

--插入数据

insert into student values(seq_s.nextVal,'石1','男');

insert into student values(seq_s.nextVal,'石2','女');

insert into student values(seq_s.nextVal,'石3','女');

insert into student values(seq_s.nextVal,'石4','女');

--查看姓名(重复的只显示一条记录)
select distinct sname from student;

--以sname分组,得到姓名和sid数量
select sname,count(sid) from student group by sname;

select sname,count(sname) from student group by sname;
select sname,count(sid),count(sname),min(sid) from student group by sname;

--以sname分组,得到重复的姓名和sid数量
select sname from student group by sname having count(sname)>1;
select sname from student group by sname having count(sid)>1;

--得到重复的所有的记录
select * from student where sname in (select sname from student group by sname having count(sid)>1);

--选出重复的最小的sid
select min(sid) from student group by sname having count(sid)>1;

--删除重复的记录，留下最小的sid
delete from student where sname in (select sname from student group by sname having count(sid)>1)
and sid not in (select min(sid) from student group by sname having count(sid)>1);