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

最新推荐文章于 2025-10-23 10:08:38 发布

Cav5lier

最新推荐文章于 2025-10-23 10:08:38 发布

阅读量2.7k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle delete query each sql

本文链接：https://blog.csdn.net/pilearn/article/details/6693888

本文介绍两种SQL查询方法来找出及处理数据库表ec_product_in中的重复记录。第一种方法使用group by与having子句快速查找重复项；第二种方法通过自连接找出重复记录并提供删除多余重复项的方法。

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