做视频网站用什么好,grace+wordpress,深圳设计公司招聘网站,咸阳市建设工程信息网在Oracle数据库中#xff0c;可以使用DELETE语句删除多个表中的数据#xff0c;也可以使用INNER JOIN、LEFT JOIN、RIGHT JOIN等连接操作来删除多个表中的数据。 下面是一些示例#xff1a;
删除两个表中相同的记录 DELETE FROM table1 WHERE EXISTS (SELECT 1 FROM table2…在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 empRowid
利用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 eGroup 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_code2.标识重复数据
select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from dict_depts_source d3.删除重复数据
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 dDELETE 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
