1.操作单个字段:
a.查询出表中单个字段的重复记录.
- SELECT * FROM aj_xyrxx t1 WHERE t1.ajbh IN
- (SELECT t2.ajbh FROM aj_xyrxx t2
- GROUP BY t2.ajbh HAVING COUNT(t2.ajbh) > 1) ;
b.删除单个字段重复的记录,只保留一个(此处可以使用oracle自带的伪列rowid或rownum).
- DELETE FROM aj_xyrxx t1 WHERE t1.ajbh IN
- (SELECT t2.ajbh FROM aj_xyrxx t2
- GROUP BY t2.ajbh HAVING COUNT(t2.ajbh) > 1) AND
- ROWID NOT IN (SELECT MIN(ROWID) FROM aj_xyrxx t3
- GROUP BY t3.ajbh HAVING COUNT(t3.ajbh) > 1) ;
2.操作多个字段
a.查询
- SELECT * FROM aj_xyrxx t1 WHERE (t1.ajbh,t1.rybh) IN
- (SELECT t2.ajbh,t2.rybh FROM aj_xyrxx t2
- GROUP BY t2.ajbh,t2.rybh HAVING COUNT(*) > 1) ;
b.删除
- DELETE FROM aj_xyrxx t1 WHERE (t1.ajbh,t1.rybh) IN
- (SELECT t2.ajbh,t2.rybh FROM aj_xyrxx t2
- GROUP BY t2.ajbh,t2.rybh HAVING COUNT(*) > 1) AND
- ROWID NOT IN (SELECT MIN(ROWID) FROM aj_xyrxx t3
- GROUP BY t3.ajbh,t3.rybh HAVING COUNT(*) > 1) ;