在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
