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