SQL删除重复记录

时间:2021-01-30 06:25:41

oracle中删除重复行的记录可以使用rowid

delete rowid,* 
from table b
where rowid!=
(select max(rowid)
from table a
where a.name=b.name)


delete rowid,* 
from table b
where rowid not in
(select max(rowid)
from table a
group by a.name)

  第二种更高效
但是sqlserver中没有 rowid,我们可以使用row_number()为其创造一个 rowid
创造测试数据
select * into copy_student from student
insert copy_student EXECUTE('select * from student' )


创建视图
create view vw_test 
as
select s#,sname,row_number() over(order by sage) rowid
from copy_student


下面我们可以把视图当成一个表来删除重复记录
delete
from vw_test
where rowid<>(select max(rowid)
from vw_test t2
where vw_test.s#=t2.s#)


或者

delete
from vw_test
where rowid not in(select max(rowid)
from vw_test
group by s#)

这种更高效

 

测试
select * from copy_student


http://blog.163.com/zangyunling@126/blog/static/164624505201062210397193/