摘要：
准备：创建新表tb_coupon/*NavicatPremiumDataTransferSourceServer：root@localhostSourceServerType：MySQLSourceServer版本：50527源主机：localhost:3306源架构：leyouTargetServerType:MySQLTargetServerVers
准备工作：新建表tb_coupon
/* Navicat Premium Data Transfer Source Server : root@localhost Source Server Type : MySQL Source Server Version : 50527 Source Host : localhost:3306 Source Schema : leyou Target Server Type : MySQL Target Server Version : 50527 File Encoding : 65001 Date: 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 id FROM tb_coupon WHERE type in( SELECT type FROM tb_coupon GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM tb_coupon GROUP BY type HAVING count(type) > 1);
4.在Mysql中是不能删除查询出来的记录，而是要通过一张临时表来解决
SELECT id from ( SELECT id FROM tb_coupon WHERE type in( SELECT type FROM tb_coupon GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM tb_coupon GROUP BY type HAVING count(type) > 1) ) as t;
5.删除type重复的数据(只保留一条，保留最小id的)
DELETE FROM tb_coupon WHERE id IN ( SELECT id from ( SELECT id FROM tb_coupon WHERE type in( SELECT type FROM tb_coupon GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM tb_coupon GROUP BY type HAVING count(type) > 1) ) as t );