Sql Server使用cursor处理重复数据过程详解
作者:whsnow 时间:2024-01-27 09:01:35
/************************************************************
* 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
(
,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
(
,OrderNo
,TrackingNo
)
VALUES
(
,@@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
标签:cursor,重复数据


猜你喜欢
MySQL execute、executeUpdate、executeQuery三者的区别
2024-01-23 15:32:11
python 进阶学习之python装饰器小结
2023-05-12 07:13:42

innerHTML在Mozilla Firefox和Opera下执行的一个特例情况。
2023-09-16 11:23:46
一文教你用Python中progress库实现进度条
2023-09-14 10:28:31

python实现简单反弹球游戏
2021-05-06 23:25:45

使用python对多个txt文件中的数据进行筛选的方法
2022-02-19 10:09:30

记录PHP错误日志 display_errors与log_errors的区别
2023-11-14 09:38:29
Python爬虫实现百度图片自动下载
2021-07-12 22:42:56

[翻译]标记语言和样式手册 Chapter 14 图片替换
2008-02-18 12:56:00

浅析JavaScriptSerializer类的序列化与反序列化
2024-02-25 17:46:14
SQL处理时间戳时如何解决时区问题实例详解
2024-01-22 15:23:11
Python容器使用的5个技巧和2个误区总结
2023-04-09 04:37:01
基于Vue实现图书管理功能
2024-04-27 16:16:59

python的即时标记项目练习笔记
2022-11-25 05:49:35

设计输入了些什么?
2008-04-02 11:16:00
Python中的闭包总结
2023-09-09 03:46:05
MySQL数据库锁机制原理解析
2024-01-17 14:29:11
一文带你深入理解Go语言中的sync.Cond
2024-04-25 15:28:48

python 随机数使用方法,推导以及字符串,双色球小程序实例
2023-10-11 08:48:31
Python 爬虫学习笔记之单线程爬虫
2021-09-23 10:47:27
