Oracle 数据去重

头真的好重好重Y

已于 2023-01-18 19:51:41 修改

阅读量8.7k

点赞数 1

分类专栏： Oracle 文章标签： oracle

于 2022-04-26 23:11:03 首次发布

本文链接：https://blog.youkuaiyun.com/zy_zhangruichen/article/details/124439600

版权

Oracle 专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍了在Oracle环境中如何查询和删除重复记录。针对单个ID字段和多个字段的情况，提供了不同的SQL语句，确保保留ROWID最小的唯一记录，同时也给出了涉及多个字段且排除ROWID最小记录的查询策略。

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

在Oracle数据库中删除重复数据

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

Person01表：
在这里插入图片描述

1. 查询表中多余的重复数据，根据ID字段来判断是否重复

SELECT * FROM PERSON01 WHERE ID IN (SELECT ID FROM PERSON01 GROUP BY ID HAVING COUNT(ID) > 1)

在这里插入图片描述

2. 根据ID字段来判断是否重复删除表中多余的重复记录、重复记录根据ID字段来判断，多条重复记录只保留 ROWID 最小的那条

DELETE FROM PERSON01 WHERE (ID) IN (SELECT ID FROM PERSON01 GROUP BY ID HAVING COUNT(ID) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM PERSON01 GROUP BY ID HAVING COUNT(*) > 1);

在这里插入图片描述

3. 查询表中多余的重复数据，根据多个字段来判断是否重复

SELECT * FROM PERSON01 A WHERE (A.ID, A.PERSON01) IN(SELECT ID, PERSON01 FROM PERSON01 GROUP BY ID, PERSON01 HAVING COUNT(*) > 1)

在这里插入图片描述

4. 删除表中多余的重复记录，根据多个字段来判断是否重复

DELETE FROM PERSON01 A WHERE (A.ID, A.PERSON01) IN (SELECT ID, PERSON01 FROM PERSON01 GROUP BY ID, PERSON01 HAVING COUNT(*) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM PERSON01 GROUP BY ID, PERSON01 HAVING COUNT(*)>1)

在这里插入图片描述

5. 查询表中多余的重复数据，根据多个字段来判断是否重复，同时不包含 ROWID 最小的记录

SELECT * FROM PERSON01 A WHERE (A.ID, A.PERSON01) IN (SELECT ID, PERSON01 FROM PERSON01 GROUP BY ID, PERSON01 HAVING COUNT(*) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM PERSON01 GROUP BY ID, PERSON01 HAVING COUNT(*)>1)

在这里插入图片描述

二，对应的DDL与DML语句

create table PERSON01
(
    ID       VARCHAR2(36),
    NAME     VARCHAR2(200),
    PERSON01 VARCHAR2(200),
    PERSON02 VARCHAR2(200),
    PERSON03 VARCHAR2(200)
)
/

TRUNCATE TABLE PERSON01;

INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('DDBFE6B0-4A1E-4B2D-8774-8B8C791C42D1', '张一三', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('DDBFE6B0-4A1E-4B2D-8774-8B8C791C42D1', '张一三', '01', '03', '01');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('DDBFE6B0-4A1E-4B2D-8774-8B8C791C42D1', '张一三', '02', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('315332A7-0A92-4BDB-8010-8254348DB52D', '李亮', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('315332A7-0A92-4BDB-8010-8254348DB52D', '李亮', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('818B8E20-8D38-40FD-B7FF-71D40410CF4D', '张建伟', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('EDFBC52C-6BAA-4F22-B015-771F801866D2', '王丽娟', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('EDFBC52C-6BAA-4F22-B015-771F801866D2', '王丽娟', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('72AD1837-39F0-4F3F-8030-45B61834A1D0', '徐家可', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('45059049-C54B-4A59-A8D9-8D4212AF1587', '琳琳', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('BBC41C7B-A22A-4C5B-8712-225014505228', '赵云龙', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('8F55D216-4921-49C7-AA85-8BABE500FD40', '欧路莎', '01', '02', '00');
INSERT INTO PERSON01(ID, NAME, PERSON01, PERSON02, PERSON03) VALUES ('8F55D216-4921-49C7-AA85-8BABE500FD40', '欧路莎', '01', '03', '00');