删除重复记录，只保留一条信息

效果如下图：

---结果：----》

1.创建学生表：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(10) DEFAULT NULL,
  `gender` varchar(4) NOT NULL DEFAULT '男',
  `age` int(3) DEFAULT '20',
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

2.插入测试数据

INSERT INTO `student` VALUES (2,'马继','女',18,1),(3,'张孟','男',23,1),(12,'吴昊','男',25,0),(13,'肖新露','女',25,0),(14,'米少东','男',23,0),(15,'白乾亮','男',20,0),(16,'米少东','男',23,0),(17,'白乾亮','男',20,0),(18,'米少东','男',23,0);

3.sql分解语句：

     #1查询重复学生的姓名

select sname from student group by sname having count(sname)>1

    #2查询重复学生中最小id。

select min(id) from student group by sname having count(sname)>1

   #3查询重复姓名的id---并且---不包含重复姓名最小id

select id from student where 
            sname in(select sname from student group by sname having count(sname)>1)
            and 
            id not in(select min(id) from student group by sname having count(sname)>1)

  # 4这里需要将 运行出来的结果，作为一个临时表。否则会报：You can't specify target ‘student’ for update in FROM clause（这个错误的意思是不能在同一表中查询的数据作为同一表的更新数据）。

          错误语句为：　　　　

delete from student where id in ( 
     select id from student where 
            sname in(select sname from student group by sname having count(sname)>1)
            and 
            id not in(select min(id) from student group by sname having count(sname)>1)
)#错误语句：报错--You can't specify target ‘student’ for update in FROM clause

         正确语句为：

delete from student where id in ( 
  select * from 
    (select id from student where 
            sname in(select sname from student group by sname having count(sname)>1)
            and 
            id not in(select min(id) from student group by sname having count(sname)>1)
    ) as mysu
)

 最终结果为：