从Mysql 数据库删除重复记录只保留其中一条(删除id最小的一条)

准备工作：新建表tb_coupon

/*Navicat Premium Data TransferSource Server         : root@localhostSource Server Type    : MySQLSource Server Version : 50527Source Host           : localhost:3306Source Schema         : leyouTarget Server Type    : MySQLTarget Server Version : 50527File Encoding         : 65001Date: 22/05/2019 18:03:38
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for tb_coupon
-- ----------------------------
DROP TABLE IF EXISTS `tb_coupon`;
CREATE TABLE `tb_coupon`  (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '优惠卷id',`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '优惠卷名称',`type` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '优惠卷类型,1、抵扣  2、折扣(打折）',`condition` bigint(20) NULL DEFAULT 0 COMMENT '抵扣或折扣条件，如果没有限制，则设置为0',`reduction` bigint(20) NULL DEFAULT 0 COMMENT '优惠金额',`discount` int(3) NULL DEFAULT 100 COMMENT '如果没有折扣，为100。如果是八五折，折扣为85',`targets` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '优惠券可以生效的sku的id拼接，以,分割',`stock` int(6) NOT NULL COMMENT '剩余优惠券数量',`start_time` datetime NOT NULL COMMENT '优惠券生效时间',`end_time` datetime NOT NULL COMMENT '优惠券失效时间',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '优惠卷表' ROW_FORMAT = Compact;-- ----------------------------
-- Records of tb_coupon
-- ----------------------------
INSERT INTO `tb_coupon` VALUES (1, 'uuu', '1', 0, 0, 100, '', 2, '1000-01-01 00:00:00', '1000-01-01 00:00:00');
INSERT INTO `tb_coupon` VALUES (2, 'uuu', '1', 0, 0, 100, '', 2, '1000-01-01 00:00:00', '1000-01-01 00:00:00');
INSERT INTO `tb_coupon` VALUES (3, 'ddd', '2', 0, 0, 100, '', 2, '1000-01-01 00:00:00', '1000-01-01 00:00:00');
INSERT INTO `tb_coupon` VALUES (4, 'ddd', '2', 0, 0, 100, '', 2, '1000-01-01 00:00:00', '1000-01-01 00:00:00');
INSERT INTO `tb_coupon` VALUES (5, 'eee', '2', 0, 0, 100, '', 2, '1000-01-01 00:00:00', '1000-01-01 00:00:00');
INSERT INTO `tb_coupon` VALUES (6, 'eee', '3', 0, 0, 100, '', 2, '1000-01-01 00:00:00', '1000-01-01 00:00:00');SET FOREIGN_KEY_CHECKS = 1;

1.查出重复的type

SELECT type FROM tb_coupon GROUP BY type HAVING count(type) > 1;

2.查出重复的type数据中最小的id

SELECT min(id) FROM tb_coupon GROUP BY type HAVING count(type) > 1;

3.查出重复的type数据中最小id的数据

SELECT * FROM tb_coupon WHERE id in(
SELECT min(id) FROM tb_coupon GROUP BY type HAVING count(type) > 1
);

4.查出重复的数据并删除