• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Sql Server使用cursor处理重复数据过程详解
    /************************************************************ 
     * 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简单查询出数据表中所有重复数据的方法
    上一篇:SqlServer实现类似Oracle的before触发器示例
    下一篇:SQL Server数据库bcp导出备份文件应用示例
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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