游标清除重复数据
有时候导数据会把数据导重,需要根据某些信息清除掉重复数据,同学做信息机业务的,不小心把用户信息导重复了,造成不能发短信。我帮他写了个游标来清理重复数据。membermobile是手机号、memberid是主键。下面是详细代码:
DECLARE @memberMobile nvarchar(255)
DECLARE @I INT
DECLARE @memberid INT
DECLARE TEST CURSOR FOR
select MemberMobile from tbl_member group by MemberMobile having count(MemberMobile)>1
SET @I = 1
OPEN TEST
FETCH NEXT FROM TEST INTO @memberMobile
WHILE @@FETCH_STATUS = 0
BEGIN
declare test2 cursor for
select memberid from tbl_member where membermobile=@memberMobile
open test2
fetch next from test2 into @memberid
while @@fetch_status=0
begin
fetch next from test2 into @memberid
if @I>1
delete from tbl_member where memberid=@memberid
set @I = @I + 1
end
close test2
deallocate test2
set @I = 1
FETCH NEXT FROM TEST INTO @memberMobile
END
CLOSE TEST
DEALLOCATE TEST
DECLARE @I INT
DECLARE @memberid INT
DECLARE TEST CURSOR FOR
select MemberMobile from tbl_member group by MemberMobile having count(MemberMobile)>1
SET @I = 1
OPEN TEST
FETCH NEXT FROM TEST INTO @memberMobile
WHILE @@FETCH_STATUS = 0
BEGIN
declare test2 cursor for
select memberid from tbl_member where membermobile=@memberMobile
open test2
fetch next from test2 into @memberid
while @@fetch_status=0
begin
fetch next from test2 into @memberid
if @I>1
delete from tbl_member where memberid=@memberid
set @I = @I + 1
end
close test2
deallocate test2
set @I = 1
FETCH NEXT FROM TEST INTO @memberMobile
END
CLOSE TEST
DEALLOCATE TEST
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步