Oracle 删除重复数据只留一条

最新推荐文章于 2025-07-02 00:06:23 发布

rayban1025

最新推荐文章于 2025-07-02 00:06:23 发布

阅读量616

点赞数

分类专栏： SQL

SQL 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用SQL查询和删除数据库表中的重复记录，包括基于单个字段和多个字段的重复记录处理方法，并提供了具体示例。

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

查询及删除重复记录的SQL语句

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

select * from 表

where Id in (select Id from 表 group byId having count(Id) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from 表 WHERE id IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1)

AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3、查找表中多余的重复记录（多个字段）

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from 表 a

where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1)

and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from 表 a

where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1)

and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

如下例子

--单个字段

create table Student(
ID NUMBER(8) PRIMARY KEY,
NAME VARCHAR(50));

INSERT INTO Student (ID,NAME) VALUES(100001,'Zhang San');
INSERT INTO Student (ID,NAME) VALUES(100002,'Li Si');
INSERT INTO Student (ID,NAME) VALUES(100003,'Zhang San');
INSERT INTO Student (ID,NAME) VALUES(100004,'Wang Wu');
INSERT INTO Student (ID,NAME) VALUES(100005,'Li Si');

select * from Student;

select rowid,stu.* from Student stu where stu.name in(select name from Student group by name having count(name)>1);

delete from Student where name in(select name from Student group by name having count(name)>1)
and ROWID NOT IN(SELECT MIN(ROWID)FROM Student group by name having count(name)>1)；

--两个字段

create table Student_Subject(
STU_ID NUMBER(8) NOT NULL,
SUB_ID NUMBER(8) NOT NULL);

INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10001,10);
INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10002,10);
INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10001,10);
INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10001,11);
INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10001,12);
INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10002,10);
INSERT INTO Student_Subject (STU_ID,SUB_ID) VALUES(10002,11);

SELECT * FROM Student_Subject order by STU_ID,SUB_ID;

--查找表中多余的重复记录(两个字段)
SELECT ROWID,ss.*
FROM Student_Subject ss
where (ss.STU_ID,ss.SUB_ID) in
(select STU_ID,SUB_ID from Student_Subject group by STU_ID,SUB_ID having count(*)>1);

--查找表中多余的重复记录（两个字段），不包含rowid最小的记录
SELECT ROWID,ss.*
FROM Student_Subject ss
WHERE (ss.STU_ID,ss.SUB_ID)in (select STU_ID,SUB_ID
from Student_Subject
group by STU_ID,SUB_ID
having count(*)>1)
and ROWID NOT IN(select min(ROWID) from Student_Subject group by STU_ID,SUB_ID having count(*)>1)
ORDER BY ss.STU_ID,ss.SUB_ID;

--删除表中多余的重复记录（两个字段），只留有rowid最小的记录
DELETE FROM Student_Subject
WHERE (STU_ID,SUB_ID) in (select STU_ID,SUB_ID
from Student_Subject
group by STU_ID,SUB_ID
having count(*)>1)
and ROWID NOT IN (select min(ROWID) from Student_Subject group by STU_ID,SUB_ID having count(*)>1);