mysql 删除重复数据，只保留一条

最新推荐文章于 2024-07-30 08:00:00 发布

「已注销」

最新推荐文章于 2024-07-30 08:00:00 发布

阅读量587

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/lunatic_xian/article/details/89706365

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql 删除重复数据，只保留一条

- 1.初始化库和数据表
- 2.删除语句

1.初始化库和数据表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for channel_daily_report
-- ----------------------------
DROP TABLE IF EXISTS `channel_daily_report`;
CREATE TABLE `channel_daily_report`  (
  `id` int(16) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `channel_id` int(16) NULL DEFAULT NULL COMMENT '英文名称',
  `channel_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '中文名称',
  `register_num` bigint(11) NULL DEFAULT NULL,
  `date_time` date NULL DEFAULT NULL COMMENT '日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 198 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of channel_daily_report
-- ----------------------------
INSERT INTO `channel_daily_report` VALUES (158, 19, 'test001', 101, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (159, 26, 'test007', 60, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (160, 14, 'dev001', 60, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (161, 19, 'test001', 101, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (162, 21, 'test003', 26, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (163, 26, 'test007', 60, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (164, 14, 'dev001', 60, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (165, 22, 'test004', 23, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (166, 21, 'test003', 26, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (167, 30, '刘总', 22, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (168, 22, 'test004', 23, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (169, 27, 'test008', 20, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (170, 30, '刘总', 22, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (171, 27, 'test008', 20, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (172, 0, '其他', 14, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (173, 0, '其他', 14, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (174, 23, 'test005', 10, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (175, 23, 'test005', 10, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (176, 28, 'test009', 9, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (177, 28, 'test009', 9, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (178, 24, 'test006', 7, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (179, 24, 'test006', 7, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (180, 12, 'qiguang002', 3, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (181, 12, 'qiguang002', 3, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (182, 13, 'mxtx002', 3, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (183, 29, 'test010', 3, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (184, 13, 'mxtx002', 3, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (185, 18, 'gzjl', 2, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (186, 29, 'test010', 3, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (187, 5, 'shcm001', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (188, 18, 'gzjl', 2, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (189, 7, 'rr001', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (190, 9, 'gzcz002', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (191, 5, 'shcm001', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (192, 25, 'yk001', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (193, 7, 'rr001', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (194, 20, 'test002', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (195, 9, 'gzcz002', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (196, 25, 'yk001', 1, '2019-04-29');
INSERT INTO `channel_daily_report` VALUES (197, 20, 'test002', 1, '2019-04-29');

SET FOREIGN_KEY_CHECKS = 1;

2.删除语句

DELETE 
FROM
	channel_daily_report
WHERE
	id IN ( 
		-- 需要生成临时表，不然会报You can't specify target table for update in FROM clause含义:不能在同一表中查询的数据作为同一表的更新数据。
		SELECT a.id 	FROM ( 
			SELECT id FROM channel_daily_report
			WHERE channel_id IN ( 
				SELECT channel_id FROM channel_daily_report
				GROUP BY channel_id, date_time HAVING COUNT( id ) > 1 
			)
		) AS a 
	) 
	AND id NOT IN (
		-- 同理
		SELECT b.id 	FROM ( 
			-- mysql5.7以上版本，使用到分组，查询字段需要是聚合使用或者参与分组
			SELECT MIN( id ) AS id FROM channel_daily_report
			GROUP BY channel_id, date_time HAVING COUNT( id ) > 1  
		) AS b
	)