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

 
 
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 @@emailVARCHAr(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