关闭

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

522人阅读 评论(0) 收藏 举报
分类:

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




0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:309878次
    • 积分:4472
    • 等级:
    • 排名:第6915名
    • 原创:116篇
    • 转载:111篇
    • 译文:0篇
    • 评论:17条
    最新评论