--1、建立表:Course
create table Course
(
ID int identity(1,1),--ID
Student varchar(20) ,--学生
Sub varchar(20) ,--课程
)
--2、插入数据
INSERT INTO Course
VALUES ('张三','语文'),('李四','语文' ),('王五','语文' )
INSERT INTO Course
VALUES ('张三','语文'),('李四','英语' ),('王五','数学' )
INSERT INTO Course
VALUES ('张三','数学'),('李四','英语' ),('王五','英语' )
INSERT INTO Course
VALUES ('张三','语文'),('李四','英语' ),('王五','数学' )
select * from Course order by ID
展示效果:
--2、Num:根据 学生、课程 汇总排序;with就是把查询的数据放到TempCourse表中
--最后对TempCourse筛选出 姓名+课程 重复的数据保留ID最小的(num>1)存入临时表 #TmpDeletedTb
;WITH TmpCourse (ID,Student,Sub,Num)
as
(
select ID,Student,Sub,ROW_NUMBER() OVER(PARTITION BY Student,Sub ORDER BY ID ) as num
from Course
)
SELECT * into #TmpDeletedTb FROM TmpCourse where num>1 order by Student,Sub,ID
select * from #TmpDeletedTb
展示效果:
--3、删除重复数据 且保留ID最小数据
delete Course from Course a
inner join #TmpDeletedTb Del on del.ID=a.ID
select * from Course
最后展示效果: