清理重复记录-优快云博客

本文提供了一种使用SQL查询来找出并删除表C_MESSAGE_T中具有重复的MSG_ID, LANGUAGE_TYPE, PROGRAM_ID组合的方法。通过ROWID确定唯一记录，并删除除ROWID最小外的所有重复项。

表 C_MESSAGE_T PK 字段为 MSG_ID, LANGUAGE_TYPE, PROGRAM_ID ，查询出所有重复值

SELECT * FROM   SFIS1.C_MESSAGE_T
WHERE   (MSG_ID, LANGUAGE_TYPE, PROGRAM_ID) IN
               ( SELECT   MSG_ID, LANGUAGE_TYPE, PROGRAM_ID
                    FROM   SFIS1.C_MESSAGE_T
                GROUP BY   MSG_ID, LANGUAGE_TYPE, PROGRAM_ID
                  HAVING   COUNT(*) > 1)

按照规则保留ROWID最小的一笔，当然如何删除还要看开发人员的建议。这里假设保留ROWID 最小的。

DELETE   FROM   SFIS1.C_MESSAGE_T
WHERE   (MSG_ID, LANGUAGE_TYPE, PROGRAM_ID) IN
               ( SELECT   MSG_ID, LANGUAGE_TYPE, PROGRAM_ID
                    FROM   SFIS1.C_MESSAGE_T
                GROUP BY   MSG_ID, LANGUAGE_TYPE, PROGRAM_ID
                  HAVING   COUNT(*) > 1)
         AND ROWID NOT IN ( SELECT   MIN (ROWID)
                               FROM   SFIS1.C_MESSAGE_T
                           GROUP BY   MSG_ID, LANGUAGE_TYPE, PROGRAM_ID
                             HAVING   COUNT(*) > 1);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-666845/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-666845/