sql 删除重复记录

测试数据准备

赋予test_user查询scott用户emp表的权限

grant select on scott.emp to test_user;

基于scott用户的EMP表,使用test_user 创建EMP表

create table emp as select * from scott.emp;

再次插入相同的数据

insert into emp select * from emp;

查询当前数据

select * from emp order by empno asc;

技术分享

可以看到每条记录出现了两次

删除重复记录

1、删除表中多余的重复记录,重复记录是根据单个字段(EMPNO)来判断,只留有rowid最小的记录

delete from emp
where empno in (select empno from emp group by empno having count(empno) > 1)
and rowid not in (select min(rowid) from emp group by empno having count(empno)>1)

2、删除表中多余的重复记录,重复记录是根据多个字段(empno ,ename)来判断,只留有rowid最小的记录

delete from emp a
where (a.empno ,a.ename) in (select empno,ename from emp group by empno,ename having count(*) > 1)
and rowid not in (select min(rowid) from emp group by empno,ename having count(*)>1)

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。