mysql删除重复数据并保留一条

SELECT * FROM dept;

#分组介绍
#SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*) > 1
SELECT 
	NAME, 
	COUNT(1)
FROM dept
GROUP BY `name`
HAVING COUNT(*) > 1

#查询全部重复的数据：
#SELECT * FROM 	表 WHERE 重复字段 IN ( SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING 	COUNT(1) > 1 	)
SELECT *
FROM 	dept
WHERE `name` IN (
		SELECT NAME
		FROM dept
		GROUP BY `name`
		HAVING 	COUNT(1) > 1
	)

#将上面的查询select改为delete（会出错）
#[Err] 1093 - You can't specify target table 'dept' for update in FROM clause
#错误原因是：更新表的同时又查询了表，查询表的同时又更新了表。mysql不支持这种更新查询同一张表的操作
DELETE
FROM 	dept
WHERE `name` IN (
		SELECT NAME
		FROM dept
		GROUP BY `name`
		HAVING 	COUNT(1) > 1
	)

#解决：把要更新的几列数据查询出来作为一个第三方表，然后筛选更新。
#问题：会把重复的数据全部删除
DELETE FROM dept WHERE `name` IN(
SELECT `name` FROM (
SELECT name FROM dept GROUP BY name HAVING  COUNT(1) > 1 
) t
)

#查询表中多余重复的数据
#这种写法正确，但是查询的速度慢，可以试试方法二
SELECT * FROM dept WHERE `name` IN (
SELECT `name` FROM dept GROUP BY `name` HAVING count(1) > 1
) AND num NOT IN (
SELECT min(num) FROM dept GROUP BY `name` HAVING count(1) > 1
)

#方法二
SELECT * FROM dept WHERE num NOT IN (
SELECT d.minnum FROM (
SELECT MIN(num) AS minnum FROM dept GROUP BY `name`
) d
)

#还有方法三
SELECT * FROM dept AS d WHERE d.id <> (
SELECT MAX(e.id) FROM dept AS e WHERE d.name = e.name
)

#删除掉重复的多余数据只保留一条
DELETE FROM dept WHERE `name` IN (
  SELECT t.`name`  FROM (
    SELECT `name`  FROM  dept GROUP BY `name` HAVING count(1) > 1
   ) t
 )
AND num NOT IN (
SELECT dt.mindeptno FROM (
  SELECT  min(num) AS mindeptno  FROM  dept  GROUP BY `name` HAVING count(1) > 1
 ) dt
)

#第二种方法（与查询的第二种方法对应）
DELETE FROM dept WHERE num NOT IN (
  SELECT dt.minno  FROM (
    SELECT MIN(num) AS minno FROM dept  GROUP BY `name`
   ) dt
 )

#第三种方法（与查询的第三种方法对应）
DELETE  FROM  dept AS ta  WHERE ta.id <> (
	SELECT t.maxid  FROM ( 
		SELECT max( tb.id ) AS maxid FROM dept AS tb WHERE ta.`name` = tb.`name` ) t 
 )



#############
#多字段的操作#
#############

#group by的字段增加为需要的字段即可

DELETE FROM dept WHERE (`name`, `function`) IN (
  SELECT
   t.`name`,
   t.`function`
  FROM (
    SELECT
     `name`,
     `function`
    FROM dept 
		GROUP BY
     `name`,
     `function`
    HAVING  count(1) > 1
   ) t
 ) AND num NOT IN (
 SELECT  dt.mindeptno FROM (
   SELECT min(num) AS mindeptno
   FROM dept  GROUP BY
    `name`,
    `function`
   HAVING count(1) > 1
  ) dt
)

demo的表结构以及数据

/*
Navicat MySQL Data Transfer

Source Server         : 本机
Source Server Version : 80022

Target Server Type    : MYSQL
Target Server Version : 80022
File Encoding         : 65001

Date: 2020-12-13 21:32:44
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `num` int DEFAULT NULL COMMENT '部门编号',
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  `function` varchar(255) DEFAULT NULL COMMENT '职能',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='部门表';

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('2', '2', '人事部', '人事工作');
INSERT INTO `dept` VALUES ('3', '3', '行政管理部', '行政工作');
INSERT INTO `dept` VALUES ('5', '4', '财务部', '财务工作');
INSERT INTO `dept` VALUES ('6', '5', '市场部', '市场工作');
INSERT INTO `dept` VALUES ('7', '6', '销售部', '销售工作');
INSERT INTO `dept` VALUES ('23', '12', '技术部', '技术工作');
INSERT INTO `dept` VALUES ('27', '13', '技术部', '技术工作');
INSERT INTO `dept` VALUES ('28', '14', '技术部', '技术工作');

在公众号里看到的一篇文章借鉴学习一下