删除数据库中重复的记录

查询重复列：

select * from tb_user where tb_name
 in (select tb_name from tb_user group by tb_name having count(tb_name)>1)

 

 

 

删除数据库中部分字段重复的记录并保留一行。

--创建视图
create view v1 as
select *,row_number() over (order by serialcode) as 'id' from TMP_CQ_Cmn_History
where starttime in
(select  starttime from TMP_CQ_Cmn_History where BranchNo=1 and SubCenterNo=5
 and  OperatorNo=2005 and CommentTime>'2010-01-30' group by starttime having count(starttime) >1 )
go
select * from v1

go

--定义游标
declare @id int
declare PJ cursor for select id from v1
open PJ
fetch next from PJ into @id  WHILE (@@FETCH_STATUS=0)
begin
delete from v1 where id=1
fetch next from PJ into @id
end
CLOSE PJ
DEALLOCATE PJ

go
drop view v1