Sql Server使用cursor处理重复数据过程详解
16lz
2022-01-10
/************************************************************ * 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之JSON 函数详解
- MySQL系列多表连接查询92及99语法示例详解教程
- Android(安卓)多媒体扫描过程（Android(安卓)Media Scanner Proces
- Android(安卓)Chromium WebView html js 开发系列
- Android(安卓)- Manifest 文件 详解
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Android(安卓)Calendar使用过程中遇到的问题
- Selector、shape详解（一）
- android2.2资源文件详解4--menu文件夹下的菜单定义