游标，反复执行

[fancydong]

写了一段语句，主要修改指定商品，指定日期的数据，但是现在想修改一时间段里的数据，请问怎么修改。
@oper_date指定的日期@item_no指定的商品 ，现在想修改一段时间的，例如8月1日至8月31日

declare @oper_date varchar(10),
@item_no varchar(20),
@branch_no varchar(2),
@sale_amt numeric(16,4)

select @oper_date = '2009-08-26'
select @item_no = '40002'

declare lcur_branch cursor for
select distinct left(branch_no,2) from t_rm_daysum where oper_date = @oper_date and item_no = @item_no

open lcur_branch
fetch lcur_branch into @branch_no
while @@fetch_status = 0
begin
--查找金额
select @sale_amt = 0
select @sale_amt = sum(sale_amt - ret_amt) from t_rm_daysum where item_no = @item_no and oper_date = @oper_date and left(branch_no,2) = @branch_no

--找最大金额来更新金额
if (@sale_amt is not null) and @sale_amt > 0
begin
update t_rm_casher_daysum set real_amt = real_amt - @sale_amt where oper_date = @oper_date and branch_no = left(@branch_no, 2)
and sale_way = 'A' and pay_way = 'RMB' and real_amt > @sale_amt
and casher_no = (select top 1 casher_no from t_rm_casher_daysum where oper_date = @oper_date and branch_no = left(@branch_no, 2)
and sale_way = 'A' and pay_way = 'RMB' and real_amt > @sale_amt order by real_amt desc)
end

fetch lcur_branch into @branch_no
end

close lcur_branch
deallocate lcur_branch

[guguda2008]

加个WHERE不就行了

[lass_name]

fetch lcur_branch into @branch_no 
修改
fetch next from lcur_branch into @branch_no 

[jwdream2008]

[Quote=引用 2 楼 lass_name 的回复:]
SQL codefetch lcur_branchinto@branch_no
修改fetchnextfrom lcur_branchinto@branch_no
[/Quote]
顶！

[fancydong]

？
时间段怎么加进去

[soft_wsx]

没有必要用游标！给点数据

[jwdream2008]

end 

fetch next from lcur_branch into @branch_no 

end 

close lcur_branch 
deallocate lcur_branch

[fancydong]

语句，不能改变语句里的效果，是按照每天来冲减的

[htl258_Tony]

一定要用游标实现吗?

[fancydong]

日期指定为2009-08-01至2009-08-31

[gw6328]

帮顶个

[fancydong]

情况比较复杂点，所以用了游标

[gw6328]

where 日期 between '2009-08-01' and '2009-08-31'?

[soft_wsx]

[Quote=引用 12 楼 jinfengyiye 的回复:]
where 日期 between '2009-08-01' and '2009-08-31'?
[/Quote]是呀！指定时间段的！
最好弄点数据出来

[zhangwonderful]

可以将游标改成如下语句，实现时间段查询过滤
declare lcur_branch cursor for
select distinct left(branch_no,2) from t_rm_daysum where oper_date between @oper_begin_date and @oper_end_date and item_no = @item_no

[lihan6415151528]

[Quote=引用 11 楼 fancydong 的回复:]
情况比较复杂点，所以用了游标

[/Quote]

复杂到什么程度了？

[华夏小卒]

declare --@oper_date varchar(10), 
  @item_no varchar(20), 
  @branch_no varchar(2), 
  @sale_amt numeric(16,4),
  @start datetime,    --新增2个
  @end datetime       --新增

select @start='2009-08-01'      --新增
select @end='2009-08-31'        --新增
--select @oper_date = '2009-08-26' 
select @item_no = '40002' 

declare lcur_branch cursor for 
select distinct left(branch_no,2) from t_rm_daysum where oper_date between @start and @end and item_no = @item_no    --修改

--下面继续
......

[华夏小卒]

fetch next lcur_branch into @branch_no    --这个也要修改

[htl258_Tony]

declare @oper_date varchar(10),  @oper_date2 varchar(10)
  @item_no varchar(20), 
  @branch_no varchar(2), 
  @sale_amt numeric(16,4) 

select @oper_date = '2009-08-01',@oper_date2 ='2009-08-31'
select @item_no = '40002' 

declare lcur_branch cursor for 
	select distinct left(branch_no,2) 
	from t_rm_daysum 
	where oper_date between @oper_date and @oper_date2 and item_no = @item_no 

open lcur_branch 
fetch lcur_branch into @branch_no 
while @@fetch_status = 0 
begin 
	--查找金额 
	select @sale_amt = 0 
	select @sale_amt = sum(sale_amt - ret_amt) 
	from t_rm_daysum 
	where item_no = @item_no 
		and oper_date between @oper_date and @oper_date2 
		and left(branch_no,2) = @branch_no 

	--找最大金额来更新金额 
	if (@sale_amt is not null) and @sale_amt > 0 
	begin 
		update t_rm_casher_daysum set 
			real_amt = real_amt - @sale_amt 
		where oper_date between @oper_date and @oper_date2
			and branch_no = left(@branch_no, 2) 
			and sale_way = 'A' and pay_way = 'RMB' and real_amt > @sale_amt 
			and casher_no = (
				select top 1 casher_no 
				from t_rm_casher_daysum 
				where oper_date between @oper_date and @oper_date2 and branch_no = left(@branch_no, 2) 
					and sale_way = 'A' and pay_way = 'RMB' and real_amt > @sale_amt 
				order by real_amt desc) 
	end 

	fetch lcur_branch into @branch_no 
end 

close lcur_branch 
deallocate lcur_branch

TRY.