mysql重複データを削除し、1つだけ保持

mysql重複データを削除し、1つだけ保持

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
	)