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

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

-- This query is editable, but generally slower 
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) 

找出需要删除的行

/* Formatted on 2011-8-17 9:59:53 (QP5 v5.114.809.3010) */
-- This query is editable, but generally slower

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