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

本篇文章主要介绍了" Sql Server使用cursor处理重复数据过程详解"，主要涉及到方面的内容，对于SqlServer感兴趣的同学可以参考一下： /************************************************************ * Code formatted b...

/************************************************************ 
 * 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使用cursor处理重复数据过程详解，包括了方面的内容，希望对SqlServer有兴趣的朋友有所帮助。

本文网址链接：http://www.codes51.com/article/detail_1867846.html