Sql Server使用cursor處理重復數據過程詳解，sqlcursor

Sql Server使用cursor處理重復數據過程詳解，sqlcursor

/************************************************************ 
 * 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裡面編寫一個存儲過程，來實現刪除一個表中的重復記錄？

/*******
假設你要處理的表名是: pludetail
可以用以下過程來實現,速度不在下面過程的考慮之中
*********/
create procedure distinct_deal
as
begin

begin transaction

select distinct * into #tempdel from pludetail --提取無重復的記錄到臨時表中

truncate table pludetail --清掉原表

insert pludetail
select * from #tempdel --把臨時表中無重復的數據插回原表
drop table #tempdel

if @@error<>0
begin
raiserror('數據處理失敗!',16,-1)
goto error_deal
end

commit transaction
return
error_deal:
rollback transaction
return

end

/**

要實現以上過程在指定時間內執行
可以用數據庫的管理中的作業作實現,很簡單,這裡不詳述了
希望這個方法對你有用

**/
 

sql server數據庫表中怎根據某個字段刪除重復數據？

我用游標實現了你的功能。

你首先建立一張空表，和你的操作表一樣的結構，但是要求是空表，沒有任何內容，比如是tempReg2

你把下面的代碼拷貝到SQL查詢分析器，稍作修改就行。
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
DECLARE Cursor_Title CURSOR FOR SELECT distinct title FROM RegMember

OPEN Cursor_Title

declare @str varchar(50)
FETCH NEXT FROM Cursor_Title Into @str

WHILE @@FETCH_STATUS = 0
BEGIN
insert into tempReg2 select top 1 * from RegMember where title=@str
FETCH NEXT FROM Cursor_Title Into @str
END

CLOSE Cursor_Title
DEALLOCATE Cursor_Title
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

我用的表名是RegMember，重復的列名是title，所以這兩個名稱需要你替換一下。別的可以不變。