Oracle 查找删除表中重复行的SQL

来源：互联网发布：淘宝不能交保证金编辑：程序博客网时间：2024/05/22 06:57

-- This query is not editable, but generally faster Select          in_type,         in_type_serial,         count(*)from     ec_product_ingroup by          in_type,         in_type_serialhaving   count(*) > 1

-- This query is editable, but generally slower Select A1.idfrom ec_product_in A1 where exists (Select 'x' from ec_product_in  A2 where A1.in_type = A2.in_typeand a1.in_type_serial= A2.in_type_serialand A1.ROWID <> A2.ROWID)-- To delete all but one of each duplicate row, -- change the first line to 'Delete' -- and change the '<>' to '>' (keeps first duplicate) --                     or '<' (keeps last duplicate)

找出需要删除的行

/* Formatted on 2011-8-17 9:59:53 (QP5 v5.114.809.3010) */-- This query is editable, but generally slowerSELECT   A1.*, A1.ROWID  FROM   ec_product_in A1 WHERE   EXISTS            (SELECT   'x'               FROM   ec_product_in A2              WHERE       A1.in_type = A2.in_type                      AND a1.in_type_serial = A2.in_type_serial                      AND A1.ROWID <> A2.ROWID)         -- To delete all but one of each duplicate row,         -- change the first line to 'Delete'         -- and change the '<>' to '>' (keeps first duplicate)         --                     or '<' (keeps last duplicate)         AND a1.id NOT IN                  (SELECT   A1.id                     FROM   ec_product_in A1                    WHERE   EXISTS                               (SELECT   'x'                                  FROM   ec_product_in A2                                 WHERE   A1.in_type = A2.in_type                                         AND a1.in_type_serial =                                               A2.in_type_serial                                         AND A1.ROWID > A2.ROWID)-- To delete all but one of each duplicate row,                                                                 -- change the first line to 'Delete'                                                                 -- and change the '<>' to '>' (keeps first duplicate)                                                                 --                     or '<' (keeps last duplicate)                  )