游标清除重复数据

最新推荐文章于 2019-03-20 18:18:01 发布

weixin_30823001

最新推荐文章于 2019-03-20 18:18:01 发布

阅读量81

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/boolean/archive/2007/06/28/798347.html

有时候导数据会把数据导重，需要根据某些信息清除掉重复数据，同学做信息机业务的，不小心把用户信息导重复了，造成不能发短信。我帮他写了个游标来清理重复数据。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

转载于:https://www.cnblogs.com/boolean/archive/2007/06/28/798347.html