oracle 删除重复记录
来源:互联网 发布:怎么算包皮正常知乎 编辑:程序博客网 时间:2024/05/21 14:45
语法:
查询及删除重复记录的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);
后续:当然也可以DELETE from 表 WHERE (id,rowid) IN ( SELECT id,min(rowid) FROM 表 GROUP BY id HAVING COUNT(id) > 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)
后续:当然也可以:
后续:当然也可以:
delete from 表 a where (a.Id,a.seq,rowid) in (select Id,seq ,max(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)
测试:
create global temporary table test2(name varchar2(100))
on commit preserve rows;
truncate table test2;
insert into test2 values('wei1');
insert into test2 values('wei1');
insert into test2 values('wei2');
insert into test2 values('wei2');
insert into test2 values('wei3');
insert into test2 values('wei3');
insert into test2 values('wei4');
delete from test2
where name in(select name from test2 d2 group by name having count(*)>1 )
and rowid in(select min(rowid) from test2 group by name having count(*)>1);
select name, rowid from test2;
测试:
create global temporary table test2(name varchar2(100))
on commit preserve rows;
truncate table test2;
insert into test2 values('wei1');
insert into test2 values('wei1');
insert into test2 values('wei2');
insert into test2 values('wei2');
insert into test2 values('wei3');
insert into test2 values('wei3');
insert into test2 values('wei4');
delete from test2
where name in(select name from test2 d2 group by name having count(*)>1 )
and rowid in(select min(rowid) from test2 group by name having count(*)>1);
select name, rowid from test2;
- oracle删除重复记录
- oracle删除重复记录
- Oracle删除重复记录
- oracle 删除重复记录
- ORACLE删除重复记录
- oracle删除重复记录
- oracle删除重复记录
- oracle 删除重复记录
- Oracle删除重复记录?
- 删除重复记录(Oracle)
- ORACLE 删除重复记录
- 【Oracle】删除重复记录
- oracle 删除重复记录
- ORACLE 删除重复记录
- oracle 删除重复记录
- oracle删除 重复记录
- oracle 删除重复记录
- ORACLE删除重复记录
- oracle 多表插入
- Android-wifi学习2---android4.0 wifi 框架
- 站位
- Flex 饼图PieChart
- 使用多个静态库在连接时出现大量函数未定义
- oracle 删除重复记录
- oracle 创建临时表的几种方法
- char型数据
- oracle 闪回查询
- apache 2.2 + tomcat 6.0.35 建立集群
- java简单易懂的文件打开,保存,另存为小程序
- 在mac下设置环境变量
- oracle delete 和 truncate
- 风雨20年:我所积累的20条编程经验