mysql 重复注册,MySQL 解决重复记录的问题

1、创建数据表并插入数据

再进行测试之前，先创建一个测试表，并插入数据。

打开Navicat for MySQL工具，运行如下代码即可：

SET FOREIGN_KEY_CHECKS=0;

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

-- Table structure for t_test

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

DROP TABLE IF EXISTS `t_test`;

CREATE TABLE `t_test` (

`id` int(11) NOT NULL,

`amount` double(255,0) DEFAULT NULL,

`user_id` int(4) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- Records of t_test

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

INSERT INTO `t_test` VALUES ('1', '1', '1');

INSERT INTO `t_test` VALUES ('2', '1', '1');

INSERT INTO `t_test` VALUES ('3', '1', '2');

INSERT INTO `t_test` VALUES ('4', '1', '2');

INSERT INTO `t_test` VALUES ('5', '1', '2');

INSERT INTO `t_test` VALUES ('6', '1', '3');

INSERT INTO `t_test` VALUES ('7', '1', '3');

INSERT INTO `t_test` VALUES ('8', '1', '4');

INSERT INTO `t_test` VALUES ('9', '1', '5');

INSERT INTO `t_test` VALUES ('10', '1', '6');

2、 测试

-- 2.1、查找表中多余的重复记录，重复记录是根据单个字段(user_id)来判断

-- select * from t_test

-- where user_id in (select user_id from t_test group by user_id having count(user_id) > 1)

-- 2.2、删除表中多余的重复记录，重复记录是根据单个字段(peopleId)来判断，只留有rowid最小的记录

-- DELETE from t_test

-- where user_id in (select user_id from t_test group by user_id having count(user_id) > 1)

-- and id not in (select min(id) from t_test group by user_id having count(user_id )>1)

-- 2.3、查找表中多余的重复记录(多个字段)

-- select * from t_test a

-- where a.user_id in (select user_id from t_test group by user_id having count(*) > 1)

-- 2.4、删除表中多余的重复记录(多个字段)，只留有rowid最小的记录

-- SELECT * from t_test a

-- where (a.user_id,a.amount) in (select user_id,amount from t_test group by user_id,amount having count(*) > 1)

-- and id not in (select min(id) from t_test group by user_id,amount having count(*)>1)

-- 2.5、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录

-- select * from t_test a

-- where (a.user_id,a.amount) in (select user_id,amount from t_test group by user_id,amount having count(*) > 1)

-- and id not in (select min(id) from t_test group by user_id,amount having count(*)>1)

-- 2.6、统计每个user_id出现的次数

-- select user_id,COUNT(user_id) from t_test group by user_id