lyfz_repos/经典版源码/document/数据库维护脚本/dindansp全部ok但dindan不ok的处理.sql

use ddf_jg
declare @id int;
declare @order nvarchar(50);
declare @count int;
declare @maxcount int;
declare @OKCount int;
declare @status nvarchar(10);

with subtbl 
as 
(
select id, count(*) as cot from dindansp where spid is not null and spid <> '' group by id 
)select id as [order],cot into #tempall from subtbl

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as id, * into #tempresult from #tempall
select @maxcount = count(id) from #tempresult
while @maxcount > 0
begin
	-- 获取指定自增列记录;
	select @order = [order], @count = cot from #tempresult where id = @maxcount
	-- 获取
	select @OKCount = count(status2) from dindansp where id = @order and status2 = 'OK' and spid is not null and spid <> ''
	-- 获取订单取件状态;
	select @status = status3 from dindan where id = @order

	if (@status <> 'OK' and @count = @OKCount)
	begin
			update dindan set status3 = 'OK' where id = @order 
			print '更新订单' + @order
			print  @count
			print  @maxcount
			print  @OKCount
	end
	set @maxcount = @maxcount -1
end

drop table #tempall
drop table #tempresult