921人阅读 评论(0)

# 操作步骤

## 一、重复记录根据单个字段来判断

1、首先，查找表中多余的重复记录，重复记录是根据单个字段（FIELD_CODE）来判断

select * from R_RESOURCE_DETAILS
where FIELD_CODE
in(select FIELD_CODE from R_RESOURCE_DETAILS
group by FIELD_CODE having count(FIELD_CODE) >1)

2、删除表中多余的重复记录，重复记录是根据单个字段（FIELD_CODE）来判断，只留有rowid最小的记录

delete from R_RESOURCE_DETAILS
where (FIELD_CODE) in (select FIELD_CODE from R_RESOURCE_DETAILS
group by FIELD_CODE having count(FIELD_CODE) >1)
and rowid not in
(select min(rowid) from R_RESOURCE_DETAILS group by FIELD_CODE having count(*)>1)

## 二、重复记录根据多个字段来判断

1、查找表中多余的重复记录（多个字段

select * from R_RESOURCE_DETAILS a
where (a.FIELD_CODE,a.DTA_ITEM_NAME)
in(select FIELD_CODE,DTA_ITEM_NAME from R_RESOURCE_DETAILS
group by FIELD_CODE,DTA_ITEM_NAME having count(*) > 1)

2、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from R_RESOURCE_DETAILS a
where (a.FIELD_CODE,a.DTA_ITEM_NAME)
in (select FIELD_CODE,DTA_ITEM_NAME from R_RESOURCE_DETAILS
group by FIELD_CODE,DTA_ITEM_NAME having count(*) > 1)
and rowid not in (select min(rowid) from R_RESOURCE_DETAILS
group by FIELD_CODE,DTA_ITEM_NAME having count(*)>1)

3、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from R_RESOURCE_DETAILS a
where (a.FIELD_CODE,a.DTA_ITEM_NAME)
in (select FIELD_CODE,DTA_ITEM_NAME from R_RESOURCE_DETAILS
group by FIELD_CODE,DTA_ITEM_NAME having count(*) > 1)
and rowid not in (select min(rowid) from R_RESOURCE_DETAILS
group by FIELD_CODE,DTA_ITEM_NAME having count(*)>1)
1
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：378980次
• 积分：5074
• 等级：
• 排名：第6412名
• 原创：154篇
• 转载：40篇
• 译文：0篇
• 评论：42条
联系方式
yangtunaiyn@gmail.com
yangtun@hotmail.com
aiynmm@163.com
850102341@qq.com
博客专栏
 React Native入门 文章：14篇 阅读：16465
 Socket网络编程 文章：5篇 阅读：4251
最新评论