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



相关阅读:
Ubuntu/Mint添加不了PPA源的原因及修复方法
原生JS实现-星级评分系统的简单实例
MySQL的源码安装及使用UDFs进行数据自动更新的教程
win7系统出现蓝屏并提示错误代码0x0000005A的原因及解决方法
Android中Activity的生命周期探讨
javascript中DOM复选框选择用法实例
javascript学习笔记之函数定义
javascript利用apply和arguments复用方法
jQuery简单实现仿京东商城的左侧菜单效果代码
详细分析使用AngularJS编程中提交表单的方式
C#中简单的拆箱操作用法实例分析
java自定义线程模型处理方法分享
解析php中获取url与物理路径的总结
如何解决局域网内mysql数据库连接慢
快速导航

Copyright © 2016 phpStudy |