oracle查询重复记录

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) ;