单字段操作

查询全部重复的数据

MySQL实现

• sql脚本

  
      Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
  
      
      
  

Oracle实现

查询表中重复数据(id最小除外)

MySQL实现

• sql脚本

  
      -- 方式一
      SELECT 
          *
      FROM
          表
      WHERE
          主键 NOT IN (
              SELECT
                  t.主键别名
              FROM
                  (
                      SELECT
                          MIN(主键) AS 主键别名
                      FROM
                          表
                      GROUP BY
                          重复字段
                  ) t
          )
  
  
      -- 方式二
      SELECT * FROM 表 AS ta WHERE ta.唯一键 <> ( SELECT min( tb.唯一键 ) FROM 表 AS tb WHERE ta.判断重复的列 = tb.判断重复的列 );
  
  
      -- 方式三(使用exists 替代 in)
      SELECT 
          *
      FROM
          表 t1
      WHERE
          NOT EXISTS (
              SELECT
                  1
              FROM
                  (
                      SELECT
                          MIN(主键) AS 主键别名
                      FROM
                          表
                      GROUP BY
                          重复字段
                  ) t2
              WHERE 
                  t2.主键别名 = t1.主键
          )
  
  

• 执行结果分析

  方式二查询的数据会比方式一少一些，因为重复的列可能存在为空的情况，导致关联失败，无法查询，mysql可以使用ifnull()函数将对应重复字段替换成指定字符，如0，Oracle使用nvl()函数。

  
      SELECT * FROM 表 AS ta WHERE ta.唯一键 <> ( SELECT min( tb.唯一键 ) FROM 表 AS tb WHERE ifnull(ta.判断重复的列,'0') = ifnull(tb.判断重复的列,'0') );
  
  
  

  在判断null==null时结果为false；故查询数据为空。Oracle中在处理千万数据之下时，可以使用内部函数nvl（null,‘0’）赋相同值判断两个空值。如果是千万以上数据建议使用isnull判断。目的是消除nvl对索引的影响。

删除表中多余重复数据并且只留1条

MySQL实现

• 方式一

  
      delete from 表 where 主键 not in (select min(主键) as 主键别名 from 表 group by 重复字段);
  
  

• 注意事项

  方式一执行会出现如下错误：[Err] 1093 - You can’t specify target table ‘dept’ for update in FROM clause

  原因是：更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作

  解决办法：把要更新的几列数据查询出来做为一个第三方表，然后筛选更新

• 方式二(推荐使用)

  
  
      delete from 表 where 主键 not in (select 表别名.主键别名 from (select min(主键) as 主键别名 from 表 group by 重复字段) 表别名) ;
  
      -- 删除username相同的重复数据，保留id最小的用户信息
      delete from 
          student 
      where
          username in (select username from ( select username from student group by username having count(username)>1) a)
      and 
          id not in ( select id from (select min(id) as id from student group by username having count(username)>1 ) b)
  
      -- 简写
      delete from student where id not in (select id from (select min(id) as id from student group by username) b);
  
  
  

• 方式三

  
      DELETE 
      FROM
          table_name AS ta 
      WHERE
          ta.唯一键 <> (
      SELECT
          t.maxid 
      FROM
          ( SELECT max( tb.唯一键 ) AS maxid FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 ) t 
          );
  

Oracle实现

多字段操作

删除表中多余重复数据并且只留1条

• 方式一

  
      DELETE
      FROM
          dept
      WHERE
          (dname, db_source) IN (
              SELECT
                  t.dname,
                  t.db_source
              FROM
                  (
                      SELECT
                          dname,
                          db_source
                      FROM
                          dept
                      GROUP BY
                          dname,
                          db_source
                      HAVING
                          count(1) > 1
                  ) t
          )
      AND deptno NOT IN (
          SELECT
              dt.mindeptno
          FROM
              (
                  SELECT
                      min(deptno) AS mindeptno
                  FROM
                      dept
                  GROUP BY
                      dname,
                      db_source
                  HAVING
                      count(1) > 1
              ) dt
      )
  
  
  

其他

• count(*)与count(1)与count(列名)异同

  count(*)与count(1)其实没有什么差别，用哪个都可以

  count(*)与count(列名)的区别

  count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入)

• in 和 exists关键字

  小表驱动大表用IN，大表驱动小表用EXISTS。IN适合的情况是外表数据量小的情况，而不是外表数据大的情况，因为IN会遍历外表的全部数据，假设a表100条，b表10000条那么遍历次数就是100*10000次，而exists则是执行100次去判断a表中的数据是否在b表中存在，它只执行了a.length次数。至于哪一个效率高是要看情况的，因为in是在内存中比较的，而exists则是进行数据库查询操作的。

参考链接

• mysql删除重复记录并且只保留一条

  https://blog.csdn.net/n950814abc/article/details/82284838

• SQL IN与EXISTS的执行效率比较

  https://www.cnblogs.com/AllUserBegin/p/3513084.html

• sql优化–in和exists效率

  https://www.cnblogs.com/weifeng123/p/9530758.html