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)
oracle 删除重复语句
最新推荐文章于 2023-11-14 16:25:33 发布