Oracle 去重复问题

1. 数据样例

1.1 创建表

create table SAMPLE_TEST
(
product_id   NUMBER,
product_name VARCHAR2(100),
product_price NUMBER(10,2),
product_desc VARCHAR2(2000)
);

comment on table SAMPLE_TEST
 is '产品信息表';
comment on column SAMPLE_TEST.product_id
 is '产品代码';
comment on column SAMPLE_TEST.product_name
 is '产品名称';
comment on column SAMPLE_TEST.product_price
 is '产品价格';
comment on column SAMPLE_TEST.product_desc
 is '产品介绍';

1.2 写入数据

insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (1, 'iPhone 11 Pro Max', 9800.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (1, 'iPhone 11 Pro Max', 9800.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (2, 'iPhone 12 Pro Max', 10888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (2, 'iPhone 12 Pro Max', 10888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (3, 'iPhone 13 Pro Max', 11888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (3, 'iPhone 13 Pro Max', 11888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (3, 'iPhone 13 Pro Max', 11888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (4, 'iPhone 14 Pro Max', 12888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (4, 'iPhone 14 Pro Max', 12888.00, '手机');
commit;
insert into sample_test (PRODUCT_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_DESC)
values (4, 'iPhone 14 Pro Max', 12888.00, '手机');
commit;

2. 查询数据

2.1 原始数据

3 . 问题情景

问题: 现需要去除重复数据,要求所有数据无论重复多少次, 只保留一条.

3.1 方法一

先将表重复数据排序编行号
然后把行号大于1的数据全部删除掉「提醒, 如果是生产库做任何数据改动要先备份」

具体实现流程如下:

3.1.1 排序


SELECT ROW_NUMBER() OVER(PARTITION BY t.product_id, t.product_name, t.product_price ORDER BY t.rowid) rn,
      t.*
 FROM sample_test t;

3.1.2 结果

3.1.3 查询删除对象

建议在删除数据前, 先查询一下, 确认查询结果, 判断where条件是否正确, 防止出现事故


SELECT *
 FROM sample_test t1
WHERE EXISTS (SELECT 1
         FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t.product_id, t.product_name, t.product_price ORDER BY t.rowid)rn,
                      t.*
                 FROM sample_test t) t2
        WHERE t2.rn > 1
          AND t1.rowid = t2.rowid);

3.1.4 删除目标数据

确认查询结果是需要删除的数据范围, 复制查询条件到Delete语句

注意‼ : 如果是生产库要记得备份数据, 同时在处理删除之前, 要想好恢复数据的方法

DELETE FROM sample_test t1
WHERE EXISTS (SELECT 1
         FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t.product_id, t.product_name, t.product_price ORDER BY t.rowid)rn,
                      t.*
                 FROM sample_test t) t2
        WHERE t2.rn > 1
          AND t1.rowid = t2.rowid);

3.2 方法二

最小或最大rowid, 先根据实际需要, 通过指定字段过滤出最小或最大rowid,「最小是旧的数据, 最大的是新的数据, 有些情况要根据实际需要变换」
然后再把上面作为过滤对象去not exists进行执行删除

3.2.1 取最小rowid

SELECT MIN(t.rowid) min_rowid FROM sample_test t GROUP BY t.product_id;

3.2.2 判断取反删除

保留此rowid对应的数据, 不在此范围内的数据删除, 先查询一下是否正确


SELECT *
 FROM sample_test t1
WHERE NOT EXISTS
(SELECT 1
         FROM (SELECT MIN(t.rowid) min_rowid FROM sample_test t GROUP BY t.product_id) t2
        WHERE t1.rowid = t2.min_rowid);

3.2.3 执行删除

确认查询结果是需要删除的数据范围, 复制查询条件到Delete语句

注意‼ : 如果是生产库要记得备份数据, 同时在处理删除之前, 要想好恢复数据的方法


DELETE
 FROM sample_test t1
WHERE NOT EXISTS
(SELECT 1
         FROM (SELECT MIN(t.rowid) min_rowid FROM sample_test t GROUP BY t.product_id) t2
        WHERE t1.rowid = t2.min_rowid);