欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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