MySQL查询及删除重复数据

1.表结构

CREATE TABLE `user_info` (
  `u_id` int(2) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `u_name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `u_gender` int(1) DEFAULT NULL COMMENT '0.女 1.男',
  `u_age` int(2) DEFAULT NULL COMMENT '年龄',
  `u_number` varchar(255) DEFAULT NULL COMMENT '学号',
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.表数据

SELECT * from user_info;

3.查询重复（学号）数据

select *,count(u_number) duplicate_count from user_info group by u_number having count(u_id)>1;

4.删除重复数据

delete from user_info where u_id not in (select a.u_id from ((select min(u_id) u_id from user_info group by u_number) a));

5.删除后查询结果