Sql Server使用cursor处理重复数据过程详解，sqlcursor

/************************************************************ * Code formatted by setyg * Time: 2014/7/29 10:04:44 ************************************************************/ CREATE PROC HandleEmailRepeat AS DECLARE email CURSOR FOR SELECT e.email ,e.OrderNo ,e.TrackingNo FROM Email20140725 AS e WHERE e.[status] = 0 ORDER BY e.email ,e.OrderNo ,e.TrackingNo BEGIN DECLARE @@email VARCHAR(200) ,@firstEmail VARCHAR(200) ,@FirstOrderNO VARCHAR(300) ,@FirstTrackingNO VARCHAR(300) ,@NextEmail VARCHAR(200) ,@@orderNO VARCHAR(300) ,@NextOrderNO VARCHAR(50) ,@@trackingNO VARCHAR(300) ,@NextTrackingNO VARCHAR(50) BEGIN OPEN email; FETCH NEXT FROM email INTO @firstEmail,@FirstOrderNO, @FirstTrackingNO; FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO; IF @NextEmail!=@firstEmail BEGIN INSERT INTO Email20140725Test ( email ,OrderNo ,TrackingNo ) VALUES ( @firstEmail ,@FirstOrderNO ,@FirstTrackingNO ); SET @@email = @NextEmail; SET @@orderNO = @NextOrderNO; SET @@trackingNO = @NextTrackingNO; END ELSE BEGIN SET @@email = @NextEmail; SET @@orderNO = @FirstOrderNO+'、'+@NextOrderNO; SET @@trackingNO = @FirstTrackingNO+'、'+@NextTrackingNO; END FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO,@NextTrackingNO WHILE @@fetch_status=0 BEGIN IF @NextEmail=@@email BEGIN IF (@NextOrderNO!=@@orderNO) SET @@orderNO = @@orderNO+'、'+@NextOrderNO PRINT 'orderNO:'+@@orderNO IF (@@trackingNO!=@NextTrackingNO) SET @@trackingNO = @@trackingNO+'、'+@NextTrackingNO PRINT 'trackingNO：'+@@trackingNO END ELSE BEGIN INSERT INTO Email20140725Test ( email ,OrderNo ,TrackingNo ) VALUES ( @@email ,@@orderNO ,@@trackingNO ); SET @@email = @NextEmail; SET @@orderNO = @NextOrderNO; SET @@trackingNO = @NextTrackingNO; END FETCH NEXT FROM email INTO @NextEmail,@NextOrderNO, @NextTrackingNO; END CLOSE email; --关闭游标 DEALLOCATE email; --释放游标 END END

怎在SQL Server里面编写一个存储过程，来实现删除一个表中的重复记录？

/*******
假设你要处理的表名是: pludetail
可以用以下过程来实现,速度不在下面过程的考虑之中
*********/
create procedure distinct_deal
as
begin

begin transaction

select distinct * into #tempdel from pludetail --提取无重复的记录到临时表中

truncate table pludetail --清掉原表

insert pludetail
select * from #tempdel --把临时表中无重复的数据插回原表
drop table #tempdel

if @@error<>0
begin
raiserror('数据处理失败!',16,-1)
goto error_deal
end

commit transaction
return
error_deal:
rollback transaction
return

end

/**

要实现以上过程在指定时间内执行
可以用数据库的管理中的作业作实现,很简单,这里不详述了
希望这个方法对你有用

**/
 

sql server数据库表中怎根据某个字段删除重复数据？

我用游标实现了你的功能。

你首先建立一张空表，和你的操作表一样的结构，但是要求是空表，没有任何内容，比如是tempReg2

你把下面的代码拷贝到SQL查询分析器，稍作修改就行。
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
DECLARE Cursor_Title CURSOR FOR SELECT distinct title FROM RegMember

OPEN Cursor_Title

declare @str varchar(50)
FETCH NEXT FROM Cursor_Title Into @str

WHILE @@FETCH_STATUS = 0
BEGIN
insert into tempReg2 select top 1 * from RegMember where title=@str
FETCH NEXT FROM Cursor_Title Into @str
END

CLOSE Cursor_Title
DEALLOCATE Cursor_Title
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

我用的表名是RegMember，重复的列名是title，所以这两个名称需要你替换一下。别的可以不变。