Sql Server使用cursor處理重復數(shù)據(jù)過程詳解
發(fā)布日期:2021-12-27 15:14 | 文章來源:CSDN
/************************************************************ * 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; --關(guān)閉游標 DEALLOCATE email; --釋放游標 END END
版權(quán)聲明:本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像,否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學習參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。
相關(guān)文章