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

最新推荐文章于 2023-11-22 09:40:11 发布

shiml1991

最新推荐文章于 2023-11-22 09:40:11 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

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

本文链接：https://blog.youkuaiyun.com/zoro_13/article/details/16975301

数据库专栏收录该内容

6 篇文章

订阅专栏

本文介绍使用SQL创建表、序列及数据插入等基本操作，并演示如何通过分组查询实现数据去重，包括查找重复记录及其处理方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--创建表

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);