Oracle 去除重复记录SQL

在Oracle数据库中，可以使用DELETE语句删除多个表中的数据，也可以使用INNER JOIN、LEFT JOIN、RIGHT JOIN等连接操作来删除多个表中的数据。
下面是一些示例：

删除两个表中相同的记录
DELETE FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

删除两个表中不同的记录
DELETE FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

使用INNER JOIN删除两个表中相同的记录
DELETE FROM table1
WHERE id IN (SELECT t1.id FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id);

---------下面的有问题

删除两个表中相同的记录，但保留一个
DELETE FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id AND ROWNUM = 1);

使用LEFT JOIN删除table1中没有匹配到table2的记录
DELETE FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id)
AND ROWNUM = 1;

使用RIGHT JOIN删除table2中没有匹配到table1的记录
DELETE FROM table2
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.id)
AND ROWNUM = 1;
 

查询及删除重复记录的SQL语句
1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断
select * from 表 where  Id in (select Id from 表 group by Id 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)

消除重复数据是数据清洗的头等大事，下面介绍比较常用的几种去重方式。

Distinct

删除重复的员工信息

select distinct(empno) from emp

Rowid

利用rowid结合max和min函数快速去重

select e.* from emp e where e.rowid = (select max(e.rowid) from emp e 

利用rowid结合max和min函数快速删除重复数据

delete e.* from emp e where e.rowid < (select max(e.rowid) from emp e

Group by

select deptno from emp group by deptno;

Row_number()

row_number是通过标记排号方式去重，如果有2条或以上的重复数据，直接筛选删除即可。

1.查看重复数据

select d.id,d.outer_code from  dict_depts_source d order by outer_code

2.标识重复数据

select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from  dict_depts_source d

3.删除重复数据

delete from dict_depts_source where id in(
select id from(select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from  dict_depts_source d)t
where t.row_flag > 1)

4.检查删除效果

select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from  dict_depts_source d

DELETE FROM table1
WHERE ROWID IN (
    SELECT rid
    FROM (
        SELECT ROWID as rid, ROW_NUMBER() OVER (PARTITION BY name ORDER BY ROWID) as rn
        FROM table1
    )
    WHERE rn > 1
);
 

ROW_NUMBER() 只管排序，其他列还可以显示

 SELECT ROWID as rid, name,dtime,info ,ROW_NUMBER() OVER (PARTITION BY name ORDER BY ROWID) as rn
 FROM table1