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
/*******
假设你要处理的表名是: 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
/**
要实现以上过程在指定时间内执行
可以用数据库的管理中的作业作实现,很简单,这里不详述了
希望这个方法对你有用
**/
我用游标实现了你的功能。
你首先建立一张空表，和你的操作表一样的结构，但是要求是空表，没有任何内容，比如是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，所以这两个名称需要你替换一下。别的可以不变。