如何删除mysql表中字段值重复的记录id小的值只保留id最大的一条记录值

  • 内容
  • 评论
  • 相关

mysql表:

 

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

 

-- ----------------------------

-- Table structure for test

-- ----------------------------

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test`  (

  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

  `order_sn` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  `order_sn_son` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  `clientkeynum` int(11) NULL DEFAULT NULL,

  `is_del` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

-- ----------------------------

-- Records of test

-- ----------------------------

INSERT INTO `test` VALUES (1, '1001', '1', 1, '1', '哈哈');

INSERT INTO `test` VALUES (2, '1001', '1', 1, '1', '呵呵呵');

INSERT INTO `test` VALUES (3, '1002', '1', 1, '1', '嘿嘿');

INSERT INTO `test` VALUES (4, '1003', '2', 1, '1', '嘎嘎');

INSERT INTO `test` VALUES (5, '1003', '4', 1, '1', '狗狗');

INSERT INTO `test` VALUES (6, '1004', '5', 1, '1', '你好');

INSERT INTO `test` VALUES (7, '1004', '5', 1, '1', '我好');

INSERT INTO `test` VALUES (8, '1004', '5', 1, '1', '他好');

 

SET FOREIGN_KEY_CHECKS = 1;

 

 

 

上表中两个字段值order_sn以及order_sn_son同时重复的结果是1,2以及6,7,8,最终实现去掉1,6,7行。