mysql删除重复数据，只保留一条有效数据

建表

CREATE TABLE `courses` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
`student` VARCHAR(255) DEFAULT NULL COMMENT '学生',
`class` VARCHAR(255) DEFAULT NULL COMMENT '课程',
`score` INT(255) DEFAULT NULL COMMENT '分数',
PRIMARY KEY (`id`),
UNIQUE KEY `course` (`student`, `class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入语句

insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '1', '90', 'A', 'Math');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '2', '80', 'A', 'Chinese');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '3', '70', 'A', 'English');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '4', '80', 'A', 'History');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '5', '73', 'B', 'Math');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '6', '60', 'B', 'Chinese');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '7', '70', 'B', 'English');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '8', '90', 'B', 'History');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '9', '70', 'C', 'Math');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '10', '50', 'C', 'Chinese');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '11', '20', 'C', 'English');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '12', '10', 'C', 'History');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '13', '53', 'D', 'Math');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '14', '32', 'D', 'Chinese');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '15', '99', 'D', 'English');
insert into `foodie-shop-dev`.`courses` ( `id`, `score`, `student`, `class`) values ( '16', '100', 'D', 'History');

查询数据

现在我只需要保留每个学生的一条成绩，不论科目
首先使用group by 进行分组，然后再留一条id最小的数据

delete from courses 
where 1=1 
and student in(  select student from courses group by student having count(*)>1))
and id not in( select ANY_VALUE(id) from courses group by student having min(id)) ;

执行报错：

Error : You can't specify target table 'courses' for update in FROM clause

意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)

将语句改为：

delete from courses 
where 1=1 
and student in( select * from ((select student from courses group by student having count(*)>1)) a )
and id not in(select * from (select ANY_VALUE(id) from courses group by student having min(id)) b );

得出结果