delete from table where rowid not in (select max(r
由热心网友提供的答案1:
大家说的没错,只需要一个循环就行了,因为你的数据都是一样,只要控制循环,执行1000次就好了。比防采集。
delete Emp p where rowid in ( select tmpid (select ROW_NUMBER() OVER(PARTITION BY id ) id ,rowid tmpid from emp ) where id <> 1 ) 可以试试
可以用SQL语句:select DISTINCT 字段名 from 表名,来实现这个问题。防采集。
扩展阅读,根据您访问的内容系统为您准备了以下扩展内容,希望对您有帮助。
oracle数据库中如何用sql语句查出重复字段以及如何删除?
查询可用group by语句,删除则用delete语句。
1、创建测试表,插入测试数据:create table test
(id int,
name varchar2(20));
insert into test values (1,'张三');
insert into test values (1,'张三');
insert into test values (2,'李四');
insert into test values (2,'李四');
insert into test values (3,'王五');
insert into test values (3,'王五');
insert into test values (3,'王五');
insert into test values (4,'赵六');
commit;
2、查询重复数据,用语句:select id,name from test group by id,name having count(*)>1;
结果:
3、删除重复记录用语句:delete from test where rowid not in (select min(rowid) from test group by id,name);
commit;
oracle某个字段有重复数据,如何删除多余数据只保留1条
Oracle 删除重复数据只留一条
查询及删除重复记录的SQL语句
1、查找表中多余的重复记录,重复记录是根据单个字段(Id)来判断
select * from 表 where Id in (select Id from 表 group byId 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)