oracle 查询及删除表中重复数据
2024-08-27 06:15:22
create table test1(
id number,
name varchar2(20)
);
insert into test1 values(1,'jack');
insert into test1 values(2,'jack');
insert into test1 values(3,'peter');
insert into test1 values(4,'red');
insert into test1 values(5,'green');
insert into test1 values(6,'green');
一 查询表中重复数据
1. 使用exists
select a.* from test1 a where exists (
select name from
( select name ,count(*)
from test1
group by name
having count(*)>1
) b
where a.name = b.name
);
2 join on
select a. * from test1 a
join (
select name ,count(*) from test1
group by name
having count(*)>1
) b
on a.name = b.name;
3 in
select a.name from test1 a
where a.name in
(
select name from test1
group by name
having count(*)>1
);
4 使用rowid 查询得到重复记录里,第一条插入记录后面的记录
select * from test1 a where rowid != (select min(rowid) from test1 b where b.name = a.name);
5 使用rowid查询得到重复记录里,最后一条记录之前插入的记录
select a.* from test1 a where rowid !=(select max(rowid) from test1 b where a.name=b.name);
6 使用rowid 查询得到 不重复的记录和重复记录里最后插入的一条记录
select a.* from test1 a where rowid =(select max(rowid) from test1 b where a.name=b.name);
7 使用rowid 查询得到不重复的记录和重复记录里最先插入的记录
select * from test1 a where rowid = (select min(rowid) from test1 b where b.name = a.name);
删除 所有重复不保留任何一条
delete from test1 a where exists ( select name from (select name ,count(*) from test1 group by name having count(*)>1) b where a.name = b.name);
delete from test1 a where a.name in (select name from test1 group by name having count(*)>1);
删除重复记录里,第一条重复记录后面插入的记录
delete from test1 a where rowid !=(select min(rowid) from test1 b where b.name = a.name);
删除先前插入的重复记录,保留最后插入的重复记录
delete from test1 a where rowid !=(select max(rowid) from test1 b where a.name=b.name);
最新文章
- Java NIO框架Mina、Netty、Grizzly介绍与对比(zz)
- 51nod1431 快乐排队
- (4) Spring中定时任务Quartz集群配置学习
- c++未指定返回值
- html笔记 横向两列布局
- rnqoj-82-又上锁妖塔-dp
- Android(java)学习笔记259:JNI之NDK开发步骤
- poj2236 基础并查集
- MVC View基础(转)
- js/css 检测移动设备方向的变化 判断横竖屏幕
- 切图教程,PS和AI切图方法分享
- c语言:union,大小端
- Django SNS 微博项目开发
- Java开发笔记(六十二)如何定义函数式接口
- docker学习(1)--基础概念
- linux下rocksdb的编译安装
- 知识点:Mysql 基本用法之事务
- 记录css的常用属性
- 配置LNPM
- java 多重继承