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.删除后查询结果