Oracle 删除重复数据只留一条

查询及删除重复记录的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);