本实例用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.删除后数据截图: