select * into t3 from t1 group by t1.[name], t1[time],t1.num,t1.price,t1.t_price declare cur cursor for select * from t3 for update of t3.num,t3.t_price open cur declare @name varchar(20),@num int,@price int,@t_price int fetch next from cur into @name,@num,@price,@t_price
declare @diff int declare @lastname varchar(20) select @diff=t2.num,@lastname=@name from t2 where t2.[name]=@name and t2.price=@price while @@fetch_status=0 begin if @lastname=@name and @diff>0 begin select @lastname=t2.[name] from t2 where t2.[name]=@name and t2.price=@price if @num-@diff<=0 begin select @diff=@diff-@num delete from t3 where current of cur end else if @num-@diff>0 begin select @diff=@diff-@num update t3 set t3.num=-(@diff),t3.t_price=t3.price*(-@diff) where current of cur end end else if @lastname<>@name begin select @diff=t2.num,@lastname=@name from t2 where t2.[name]=@name and t2.price=@price if @num-@diff<=0 begin select @diff=@diff-@num delete from t3 where current of cur end else if @num-@diff>0 begin select @diff=@diff-@num update t3 set t3.num=- (@diff),t3.t_price=t3.price*(-@diff) where current of cur
end end fetch next from cur into @name,@num,@price,@t_price end close cur deallocate cur got3是临时表,因为在游标中有group by后会被隐式转换为READ ONLY,无法更新
procedure 声明省略 变量声明省略DECLARE t2_cursor CURSOR GLOBAL FOR select name, per_price, qty from t2
OPEN t2_cursor
FETCH NEXT FROM t2_cursor INTO @name, @per_price, @qty
WHILE @@FETCH_STATUS = 0
BEGIN
select @t1_sum_qty = sum(t1.qty) from t1 where name = @name and per_price = @perprice -------- t1总和大于t2时, IF @t1_sum_qty >= @qty
DECLARE t1_cursor CURSOR GLOBAL FOR select name, per_price, qty from t1 where name = @name and per_price = @per_price order by date
OPEN t1_cursor
FETCH NEXT FROM t1_cursor INTO @t1_name, @t1_per_price, @t1_qty
@init_qty = @qty -- initial value
WHILE @@FETCH_STATUS = 0 BEGIN IF @init_qty > 0 begin
IF @t1_qty <= @init_qty begin delete from t1 where name = @t1_name and per_price = @t1_per_price @init_qty = @init_qty - @t1_qty end else begin update t1 set qty = qty - @qty where name = @t1_name and per_price = @t1_per_price @init_qty = 0 end
end
FETCH NEXT FROM t1_cursor INTO @t1_name, @t1_per_price, @t1_qty
END
CLOSE t1_cursor DEALLOCATE t1_cursor END ELSE BEGIN t1总和小于t2时 省略 END FETCH NEXT FROM t2_cursor INTO @name, @per_price, @qty
当T2的总价比T1的总价大时,两者相进,T1的总价就为0吗,此时T2的总价是不是为两者差?说具体点,
KKKKKKKKKKKK
但有一个条件就是,先用T1中符合T2记录条件的(名称,单价相同的)最老的一条记录减~如果不够,在用排在第二老的减~一直减完T2的数量!
——————————————————————————————————对~~
就是先进先出!!给个方案~~~
参考一下~~
我没有做过!不知道应该怎么写!!
谢谢!
大家多指教create proc up_update as
select * into t3 from t1 group by t1.[name],
t1[time],t1.num,t1.price,t1.t_price
declare cur cursor
for
select * from t3
for update of t3.num,t3.t_price
open cur
declare @name varchar(20),@num int,@price int,@t_price int
fetch next from cur into @name,@num,@price,@t_price
declare @diff int
declare @lastname varchar(20)
select @diff=t2.num,@lastname=@name from t2
where t2.[name]=@name and t2.price=@price
while @@fetch_status=0
begin
if @lastname=@name and @diff>0
begin
select @lastname=t2.[name] from t2 where t2.[name]=@name and t2.price=@price
if @num-@diff<=0
begin
select @diff=@diff-@num
delete from t3
where current of cur
end
else
if @num-@diff>0
begin
select @diff=@diff-@num
update t3 set t3.num=-(@diff),t3.t_price=t3.price*(-@diff) where current of cur
end
end
else
if @lastname<>@name
begin
select @diff=t2.num,@lastname=@name from t2
where t2.[name]=@name and t2.price=@price
if @num-@diff<=0
begin
select @diff=@diff-@num
delete from t3
where current of cur
end
else
if @num-@diff>0
begin
select @diff=@diff-@num
update t3
set t3.num=- (@diff),t3.t_price=t3.price*(-@diff)
where current of cur
end
end
fetch next from cur into @name,@num,@price,@t_price
end
close cur
deallocate cur
got3是临时表,因为在游标中有group by后会被隐式转换为READ ONLY,无法更新
有点模糊,能说明一下工作流程么?谢谢!
有点问题~~
象你这样处理的话!
T1表中数据怎么办?
这样做的话!T3中的数据怎么回到T1中去呢?
要是回不去~当这个游标执行第2次的话!
取的不还是相同的数据?
和没有做没有什么区别啊!
唉~~~~~~~
变量声明省略DECLARE t2_cursor CURSOR
GLOBAL
FOR
select name, per_price, qty from t2
OPEN t2_cursor
FETCH NEXT FROM t2_cursor
INTO @name, @per_price, @qty
WHILE @@FETCH_STATUS = 0
BEGIN
select @t1_sum_qty = sum(t1.qty) from t1
where name = @name and per_price = @perprice
-------- t1总和大于t2时,
IF @t1_sum_qty >= @qty
DECLARE t1_cursor CURSOR
GLOBAL
FOR
select name, per_price, qty from t1
where name = @name and per_price = @per_price
order by date
OPEN t1_cursor
FETCH NEXT FROM t1_cursor
INTO @t1_name, @t1_per_price, @t1_qty
@init_qty = @qty -- initial value
WHILE @@FETCH_STATUS = 0
BEGIN
IF @init_qty > 0
begin
IF @t1_qty <= @init_qty
begin
delete from t1 where name = @t1_name and per_price = @t1_per_price
@init_qty = @init_qty - @t1_qty
end
else
begin
update t1 set qty = qty - @qty where name = @t1_name and per_price = @t1_per_price
@init_qty = 0
end
end
FETCH NEXT FROM t1_cursor
INTO @t1_name, @t1_per_price, @t1_qty
END
CLOSE t1_cursor
DEALLOCATE t1_cursor
END
ELSE
BEGIN
t1总和小于t2时 省略
END FETCH NEXT FROM t2_cursor
INTO @name, @per_price, @qty
END
CLOSE t2_cursor
DEALLOCATE t2_cursor