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

来源：互联网发布：java第三版课后题答案编辑：程序博客网时间：2024/05/19 00:10

本实例用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) bon a.id=b.id and a.name=b.nameand a.autoid>b.autoid

5.删除后数据截图：