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

2024-04-09 19:23:03 411

/************************************************************ *Codeformattedbysetyg *Time:2014/7/2910:04:44 ************************************************************/ CREATEPROCHandleEmailRepeat AS DECLAREemailCURSOR FOR SELECTe.email ,e.OrderNo ,e.TrackingNo FROMEmail20140725ASe WHEREe.[status]=0 ORDERBY e.email ,e.OrderNo ,e.TrackingNo BEGIN DECLARE@@emailVARCHAR(200) ,@firstEmailVARCHAR(200) ,@FirstOrderNOVARCHAR(300) ,@FirstTrackingNOVARCHAR(300) ,@NextEmailVARCHAR(200) ,@@orderNOVARCHAR(300) ,@NextOrderNOVARCHAR(50) ,@@trackingNOVARCHAR(300) ,@NextTrackingNOVARCHAR(50) BEGIN OPENemail; FETCHNEXTFROMemailINTO@firstEmail,@FirstOrderNO,@FirstTrackingNO; FETCHNEXTFROMemailINTO@NextEmail,@NextOrderNO,@NextTrackingNO; IF@NextEmail!=@firstEmail BEGIN INSERTINTOEmail20140725Test ( 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 FETCHNEXTFROMemailINTO@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 INSERTINTOEmail20140725Test ( email ,OrderNo ,TrackingNo ) VALUES ( @@email ,@@orderNO ,@@trackingNO ); SET@@email=@NextEmail; SET@@orderNO=@NextOrderNO; SET@@trackingNO=@NextTrackingNO; END FETCHNEXTFROMemailINTO@NextEmail,@NextOrderNO,@NextTrackingNO; END CLOSEemail;--关闭游标 DEALLOCATEemail;--释放游标 END END

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

热门推荐

随机推荐