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

/************************************************************* Code formatted by setyg* Time: 2014/7/29 10:04:44************************************************************/CREATE PROC HandleEmailRepeat ASDECLARE email CURSORFOR SELECT e.email,e.OrderNo,e.TrackingNo FROMEmail20140725 AS e WHERE e.[status] = 0 ORDER BY e.email,e.OrderNo,e.TrackingNoBEGIN DECLARE @@email VARCHAR（200） ,@firstEmail VARCHAR（200） ,@FirstOrderNOVARCHAR（300） ,@FirstTrackingNO VARCHAR（300） ,@NextEmailVARCHAR（200） ,@@orderNOVARCHAR（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; ENDFETCH NEXT FROM email INTO @NextEmail,@NextOrderNO,@NextTrackingNO WHILE @@fetch_status=0 BEGIN IF @NextEmail=@@email BEGIN IF （@NextOrderNO！=@@orderNO） SET @@orderNO = @@orderNO+"、"+@NextOrderNOPRINT "orderNO:"+@@orderNO IF （@@trackingNO！=@NextTrackingNO） SET @@trackingNO = @@trackingNO+"、"+@NextTrackingNOPRINT "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