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/