SQL删除重复记录

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/