Oracle删除重复记录只保留一条数据的几种方法

Question

如题所述

Answer 1

你好：一种简单方式就是直接根据你不想要的数据进行分组后存储到另外一张表里面
create table as select * from b group id;
可以写存储过程来实现判断重复数据后删除。
另外一种方法就是插入的时候直接设置主见不让他插入，直接提示不能插入。
这个可以参考资料：http://blog.csdn.net/haiross/article/details/38513247

Answer 2

oracle中查询及删除重复记录的SQL语句：
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
SELECT *
FROM people
WHERE peopleid IN
(SELECT peopleid FROM people GROUP BY peopleid HAVING COUNT(peopleid) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（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)
注:rowid为oracle自带不用该.....
3、查找表中多余的重复记录（多个字段）
SELECT *
FROM vitae a
WHERE (a.peopleid, a.seq) IN (SELECT peopleid,
seq
FROM vitae
GROUP BY peopleid,
seq
HAVING COUNT(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有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)