Sql Server使用cursor处理重复数据过程详解
程序员文章站
2023-11-30 10:08:40
/************************************************************
* code formatted...
/************************************************************ * 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
下一篇: PHP中用Trait封装单例模式的实现
推荐阅读
-
Sql Server使用cursor处理重复数据过程详解
-
SQL Server中通用数据库角色权限的处理详解
-
SQL server 重复数据处理 (根据条件筛选符合merge的数据)
-
sql server 2008 数据库管理系统使用SQL语句创建登录用户步骤详解
-
使用SQL SERVER存储过程实现历史数据迁移方式
-
SQL Server数据库启动过程详解及启动不起来的问题分析及解决方法
-
SQL Server数据库ROW_NUMBER()函数使用详解
-
SQL Server 数据库SQL语句中CURSOR(游标)的使用
-
SQL Server数据库启动过程详解及启动不起来的问题分析及解决方法
-
使用SQL Server存储过程将BLOB/Image等图片或二进制数据另存为磁盘文件