Oracle 重复数据查询以及删除
来源:互联网 发布:修身夹克 知乎 编辑:程序博客网 时间:2024/05/22 06:53
Create table test
(id number(2),
names varchar2(20));
insert into test values(1,'张三');
insert into test values(2,'李四');
insert into test values(3,'马七');
select * from test;
--查找重复数据
select * from test a where (a.id,a.names) in
(select id,names from test group by id,names having count(*) > 1)
--删除重复数据,只留rowid最小的那行;
delete from test a where (a.id,a.names) in
(select id,names from test group by id,names having count(*) > 1)
and rowid not in (select min(rowid) from test group by id,names having count(*)>1)
--查找重复数据,不含rowid最小的行
select * from test a where (a.id,a.names) in
(select id,names from test group by id,names having count(*) > 1)
and rowid not in (select min(rowid) from test group by id,names having count(*)>1)
(id number(2),
names varchar2(20));
insert into test values(1,'张三');
insert into test values(2,'李四');
insert into test values(3,'马七');
select * from test;
--查找重复数据
select * from test a where (a.id,a.names) in
(select id,names from test group by id,names having count(*) > 1)
--删除重复数据,只留rowid最小的那行;
delete from test a where (a.id,a.names) in
(select id,names from test group by id,names having count(*) > 1)
and rowid not in (select min(rowid) from test group by id,names having count(*)>1)
--查找重复数据,不含rowid最小的行
select * from test a where (a.id,a.names) in
(select id,names from test group by id,names having count(*) > 1)
and rowid not in (select min(rowid) from test group by id,names having count(*)>1)
---1.以上是重复数据根据多列来判断
以下是重复数据根据单列来判断
1、首先,查找表中多余的重复记录,重复记录是根据单个字段(id)来判断
select * from test where id in(select id from test group by having count(id) >1)
2、删除表中多余的重复记录,重复记录是根据单个字段(id)来判断,只留有rowid最小的记录
delete from test where (id) in (select id from test group by id having count(id) >1) and rowid not in (select min(rowid) from test group by id having count(*)>1)
0 0
- Oracle 重复数据查询以及删除
- oracle 查询重复数据以及删除重复数据
- oracle查询重复数据与删除重复记录方法
- oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录方法
- Oracle查询重复数据与删除重复记录
- Oracle查询重复数据与删除重复记录方法
- oracle 查询重复数据并且删除, 只保留一条数据
- oracle删除重复数据
- Oracle删除重复数据
- nyoj 536 开心的mdd(最优矩阵链乘)dp
- EffectiveJava(15)强化对象和域的不可变性
- HashMap、HashTable与ConcurrentHashMap的区别
- python抓取网页图片
- 6.NLTK之学习文本分类
- Oracle 重复数据查询以及删除
- Python异常处理机制
- Android文档Training之Fragment
- 矩阵乘法测试
- 欢迎使用CSDN-markdown编辑器
- 编码格式
- 99%用错销售技巧笔记
- js兼容性问题
- 5-12 排序 (25分)