ORACLE 重复记录相关操作

----A:保留id最大的行,删除其它行

--方法1

DELETE USER FROM USER T INNER JOIN (SELECT NAME, MAX(ID) AS ID
                                      FROM USER
                                     GROUP BY NAME) A ON T.NAME = A.NAME AND T.ID <> A.ID   

--方法2 

DELETE USER FROM USER T
 WHERE EXISTS (SELECT *
          FROM USER
         WHERE NAME = T.NAME
           AND ID > T.ID)

----B:保留id最小的行,删除其它行     

--方法1

DELETE USER FROM USER T INNER JOIN (SELECT NAME, MIN(ID) AS ID
                                      FROM USER
                                     GROUP BY NAME) A ON T.NAME = A.NAME AND T.ID <> A.ID

--方法2

DELETE USER FROM USER T
      WHERE EXISTS (SELECT *
               FROM USER
              WHERE NAME = T.NAME
                AND ID < T.ID)

----C:删除所有重复的name行  

DELETE USER FROM USER T INNER JOIN (SELECT ID
                                      FROM USER A
                                     WHERE EXISTS
                                     (SELECT *
                                              FROM USER
                                             WHERE NAME = A.NAME
                                             GROUP BY NAME
                                            HAVING COUNT(*) > 1)) AS B ON T.ID = B.ID


---实例:file_name,length重复记录根据file_id大的值查出:
SELECT T1.*
  FROM PICK_LOG T1
 WHERE FILE_ID IN (SELECT MAX(FILE_ID)
                     FROM PICK_LOG T2
                    WHERE T1.FILE_NAME = T2.FILE_NAME
                      AND T1.LENGTH = T2.LENGTH
                    GROUP BY T2.FILE_NAME,T2.LENGTH
                   HAVING COUNT(*) > 1)