爱站程序员基地准备工作：新建表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 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);
