sql删除几个字段值相同的重复列

本实例用sqlserver:

1.创建数据表

create table course(
	autoid int primary key, --主键
    id int, --编号
    name varchar(20), --课程名称
    score int,
    teacher varchar(20)
)

2.插入测试数据

insert into course(autoid,id,name,score,teacher)
values(1,1,'english',4,'ta');
insert into course(autoid,id,name,score,teacher)
values(2,1,'english',5,'tb');
insert into course(autoid,id,name,score,teacher)
values(3,2,'math',4,'ta');
insert into course(autoid,id,name,score,teacher)
values(4,2,'math',4,'ta');
insert into course(autoid,id,name,score,teacher)
values(5,3,'chinese',4,'ta');
insert into course(autoid,id,name,score,teacher)
values(6,4,'art',4,'ta');

3.数据截图如下所示：

4.删除id和name重复的数据列(保留autoid主键小的)

delete a 
from course a inner join
(select id,name,min(autoid) as autoid from course group by id,name having count(1)>1) b
on a.id=b.id 
and a.name=b.name
and a.autoid>b.autoid

5.删除后数据截图：