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

Glenna ·

更新时间:2024-11-15

· 735 次阅读


/************************************************************ 
 * 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数据库删除数据集中重复数据实例讲解sqlserver清除完全重复的数据只保留重复数据中的第一条SQL Server中删除重复数据的几个方法sqlserver中重复数据值只取一条的sql语句sqlserver合并DataTable并排除重复数据的通用方法分享教你几种在SQLServer中删除重复数据方法MSSql简单查询出数据表中所有重复数据的方法

复数 SQL SQL Server 数据 cursor

1024 个赞