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

数据运维 2023-04-19 LOVEHL^ˇ^ 手机阅读

/**** Code formatted by setyg Time: 2014/7/29 10:04:44 ****/ CREATE PROC HandleEmailRepeat AS DECLARE email CURSOR FOR SELEC

/****

  • 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