lyfz_repos/经典版源码/document/数据库维护脚本/从选片日志里恢复指定订单和时间的选片记录.sql

--从选片日志里恢复指定的选片情况;
use [db]
declare @time varchar(50)		-- 选片时间;
declare @order varchar(50)		-- 选片订单;
declare @spid varchar(50)		-- 商品id;
declare @spname varchar(50)     -- 商品名;
declare @no varchar(max)		-- 商品相片;
declare @type varchar(50)		-- 日志类型;
declare @ren varchar(50)		-- 选片人;
declare @count int              -- 该订单、时间的选片日志记录数;
declare @ary Table				-- 表变量,存储符合条件的结果;
(
	aid		int identity(1,1) not null,
	id		varchar(50) null,
	spid	varchar(50) null,
	spname  varchar(50) null,
	imgno	varchar(max) null
)

-- 请在此处设置要查询的记录信息(前三项可改，根据要恢复的记录修改);
set @ren = '王艳'					-- 选片人;
set @time = '2015-09-25 06:49:02'	-- 选片记录时间;
set @order = '20141023-003'			-- 订单号;	
set @type = '选片保存'				-- 勿改;

-- 获取符合条件的记录数量;
select @count = count(*) from [dbo].[SelectPhotoLog] where LogDate = @time and OrderId = @order and OptType = @type and UserName = @ren
print '共查询 [' + convert(varchar, @count) + '] 条记录, 选片人[' + @ren +']'

-- 将符合条件的记录存储到表变量中;
--select OrderId, SPID, SPName, PhotoNo into @ary from [dbo].[SelectPhotoLog] where LogDate = @time and OrderId = @order and OptType = @type and UserName = @ren
insert into @ary(id, spid, spname, imgno) select OrderId, SPID, SPName, PhotoNo from [dbo].[SelectPhotoLog] where LogDate = @time and OrderId = @order and OptType = @type and UserName = @ren

-- 根据表变量来依次更新dindansp表;
while @count > 0
begin
	select @spid = spid, @spname = spname, @no = imgno from @ary where aid = @count
	print '订单号：' + @order + ' 商品ID:' + @spid + ' 商品名：' + @spname + ' 商品相片：' + @no
	update [dbo].[dindansp] set [no] = @no where [spid] = @spid and [id] = @order and [name] = @spname
	set @count = @count - 1
end