刪除數(shù)據(jù)庫中重復(fù)的記錄
查詢重復(fù)列:
select * from tb_user where tb_name
in (select tb_name from tb_user group by tb_name having count(tb_name)>1)
in (select tb_name from tb_user group by tb_name having count(tb_name)>1)
刪除數(shù)據(jù)庫中部分字段重復(fù)的記錄并保留一行。
--創(chuàng)建視圖
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
--定義游標(biāo)
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

浙公網(wǎng)安備 33010602011771號