1.部分重复(表中有主键)
学生表(student)
字段:id,xh(学号),xm(姓名),nj(年级)
表中含有部分字段重复(xm姓名,nj年级),可以通过主键来删除重复数据
--姓名重复--获取重复姓名的学生信息(其中一条数据)Select a.idFrom (Select v.*, Row_Number() Over(Partition By v.xm Order By v.xm) NmFrom student v) aWhere a.Nm > 1;--删除数据delete from student v where 1=1 and Exists (select 1 from (Select a.idFrom (Select v.*, Row_Number() Over(Partition By v.xm Order By v.xm) NmFrom student v) aWhere a.Nm > 1) where a.id = v.id )
2.完全重复(表没有主键)
由于某些表的特殊性没有主键,需要删除完全重复的数据且只保留一条
利用表的rowid 来删除数据
--获取重复数据的 rowidSelect a.id,a.rowidFrom (Select v.*, Row_Number() Over(Partition By v.Id Order By v.Id) NmFrom student v) aWhere a.Nm > 1 Order By a.Id--删除数据Delete From student v Where 1=1 And Exists (Select 1 from (Select a.id,a.rowidFrom (Select v.*, Row_Number() Over(Partition By v.Id Order By v.Id) NmFrom student v) aWhere a.Nm > 1 Order By a.Id) b Where b.rowid = v.rowid)
