sqlserver刪除重複行

1.建表測試用
create table denny_repeate(empno varchar(20),enname varchar(20))
insert into denny_repeate values('K1007F86','DENNY'),('K1007F86','DENNY'),('K1007F86','DENNY'),('K1007F86','DENNY'),('K1007C90','KEN'),('K1007C90','KEN'),('K1007C90','KEN'),('K1007C90','KEN'),('K1007C90','KEN'),('K0404103','ALEX')

2.查詢
select * from denny_repeate

顯示
K1007F86 DENNY
K1007F86 DENNY
K1007F86 DENNY
K1007F86 DENNY
K1007C90 KEN
K1007C90 KEN
K1007C90 KEN
K1007C90 KEN
K1007C90 KEN
K0404103 ALEX
K1007F86 DENNY
K1007C90 KEN
K0404103 ALEX

3.作業刪除
declare @empno varchar(20),@enname varchar(20),@sm int;
declare del_cur cursor for select empno,enname,count(empno)-1 from denny_repeate group by empno,enname having count(empno)>1;
open del_cur;
fetch next from del_cur into @empno,@enname,@sm;
while @@FETCH_STATUS =0
begin
  delete a from (select top (@sm) * from denny_repeate where empno=@empno) a;
  fetch next from del_cur into @empno,@enname,@sm;
end
close del_cur;
deallocate del_cur;
4.刪除完成
再查詢，結果
K1007F86 DENNY
K1007C90 KEN
K0404103 ALEX

one-SQL

delete table where f_id in(
select min(idfield) from table with(nolock) group by field having count(field)>1)