DB2删除重复记录的五种情况

数据库
如果数据库中有重复的冗余记录,我们需要做的就是查找出来并进行删除操作,下面就有关DB2删除重复记录的情况进行了详述,供您参考学习。

DB2删除重复记录得方法和其他数据库系统有何不同呢?下文为您列举了五种DB2删除重复记录的情况,希望对您有所启迪。

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、DB2删除重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、DB2删除重复记录(多个字段),只留有rowid最小的记录

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

 

 

 

【编辑推荐】

aix环境下创建DB2数据库的方法

九个DB2常用命令

aix下DB2创建表空间的方法

windows下DB2创建表空间的实现

教您如何在DB2删除数据量大的表

责任编辑:段燃 来源: 互联网
相关推荐

2010-09-28 15:46:22

SQL删除重复记录

2010-09-28 15:40:51

SQL删除重复记录

2010-10-13 17:07:46

MySQL删除重复记录

2015-10-23 16:40:21

DB2删除数据

2010-11-03 13:50:49

DB2删除重复数据

2010-09-03 09:49:39

SQL删除

2010-11-23 14:26:02

MySQL删除重复记录

2010-10-27 16:49:23

Oracle删除重复记

2010-09-25 16:17:25

SQL语句

2010-10-27 16:56:05

Oracle重复记录

2010-11-03 11:49:15

删除DB2数据

2010-11-03 15:49:32

DB2删除表

2010-11-01 09:54:24

DB2管理服务器

2010-07-26 17:00:11

SQL Server查

2011-05-24 10:04:39

Oracle重复记录

2010-10-13 17:13:17

MySQL重复记录

2010-11-15 14:42:03

Oracle查询重复记

2010-09-03 11:42:04

SQL删除

2010-11-25 15:43:02

MYSQL查询重复记录

2010-09-30 16:21:40

DB2隔离级别
点赞
收藏

51CTO技术栈公众号