oracle查找重复数据和删除重复数据sql-优快云博客

本文介绍了一种使用SQL查询和删除数据库中重复记录的方法。通过分组和计数功能定位重复项，并提供删除多余记录的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查找重复数据sql(思路就是根据需要判断重复数据的字段分组，根据having大于2的就是重复的)

--查找某表重复数据
select BUSS_TYPE_ID,
         BUSS_TYPE,
         TRADE_VARIETY_ID,
         TRADE_VARIETY,
         TRADE_SUBVARIETY_ID,
         TRADE_SUBVARIETY,
         TRADE_CODE,
         TRADE_OBJECT,
         count(id)
    from T_RPT_BUSINESS_STRUCTURE
    where jys_id=6
   group by BUSS_TYPE_ID,
            BUSS_TYPE,
            TRADE_VARIETY_ID,
            TRADE_VARIETY,
            TRADE_SUBVARIETY_ID,
            TRADE_SUBVARIETY,
            TRADE_CODE,
            TRADE_OBJECT
       having count(id) >= 2;

删除重复数据的sql

       
delete from T_RPT_BUSINESS_STRUCTURE where 
       (BUSS_TYPE_ID,
         BUSS_TYPE,
         TRADE_VARIETY_ID,
         TRADE_VARIETY,
         TRADE_SUBVARIETY_ID,
         TRADE_SUBVARIETY,
         TRADE_CODE,
         TRADE_OBJECT)
   in(
         select BUSS_TYPE_ID,
         BUSS_TYPE,
         TRADE_VARIETY_ID,
         TRADE_VARIETY,
         TRADE_SUBVARIETY_ID,
         TRADE_SUBVARIETY,
         TRADE_CODE,
         TRADE_OBJECT
        from T_RPT_BUSINESS_STRUCTURE
       group by BUSS_TYPE_ID,
                BUSS_TYPE,
                TRADE_VARIETY_ID,
                TRADE_VARIETY,
                TRADE_SUBVARIETY_ID,
                TRADE_SUBVARIETY,
                TRADE_CODE,
                TRADE_OBJECT
           having count(id) >= 2
   ) and 
                id not in (
                   select min(id)
                  from T_RPT_BUSINESS_STRUCTURE
                 group by BUSS_TYPE_ID,
                          BUSS_TYPE,
                          TRADE_VARIETY_ID,
                          TRADE_VARIETY,
                          TRADE_SUBVARIETY_ID,
                          TRADE_SUBVARIETY,
                          TRADE_CODE,
                          TRADE_OBJECT
                     having count(id) >= 2
                )