如此SQL語句能否實現？
表A
CNo SeqNo sNum dNum
A 1 20
A 2 50
A 3 30
表B
CNo Num
A 80
需得到的結果：
CNo SeqNo sNum dNum
A 1 20 20
A 2 50 50
A 3 30 10
也就是依次更新A表中的dNum的值，當B表中的Num大於等B表的數時，A表中的dNum=sNum,當小於時A中的dNum等於B表中的Num(余下的數)
[解决办法]
當然可以
[解决办法]
create table 表A(CNo varchar(100), SeqNo int, sNum int, dNum int)
insert into 表A select 'A ', 1, 20,null
insert into 表A select 'A ', 2, 50,null
insert into 表A select 'A ', 3, 30,null
create table 表B (CNo varchar(100), Num int)
insert into 表B select 'A ', 80
select *
from 表A
update 表A set dNum =
case
when sNum <B.Num-isnull((select sum(sNum) from 表A where CNo=a.CNo and SeqNo <a.SeqNo),0) then sNum
else isnull((B.Num-(select sum(sNum) from 表A where CNo=a.CNo and SeqNo <a.SeqNo)),0)
end
from 表A as a
inner join 表B as b on a.CNo=B.CNo
select *
from 表A
drop table 表A,表B
[解决办法]
declare @i int
update A,B set A.dNum=case @i=((@i=B.Num)-A.sNum) when > 0 then A.sNum else @i where A.CNo=B.CNo
试试.
[解决办法]
declare @A table(CNo char(1), SeqNo int, sNum int, dNum int)
insert @A
select 'A ', 1,20,null union all
select 'A ', 2,50,null union all
select 'A ', 3,30,null
declare @B table(CNo char(1), Num int)
insert @B
select 'A ',80
--这是SELECT
select a.CNo,a.SeqNo,a.sNum,
dNum=
case
when b.Num> (select sum(sNum) from @A where SeqNo <=a.SeqNo)
then (select sum(sNum) from @A where SeqNo <=a.SeqNo)
else
b.Num - (select sum(sNum) from @A where SeqNo <a.SeqNo)
end
from @A a, @B b where a.Cno = b.Cno
/*
A12020
A25070
A33010
*/
--这是UPDATE
update a set a.dNum =
case
when b.Num> (select sum(sNum) from @A where SeqNo <=a.SeqNo)
then (select sum(sNum) from @A where SeqNo <=a.SeqNo)
else
b.Num - (select sum(sNum) from @A where SeqNo <a.SeqNo)
end
from @A a, @B b where a.Cno = b.Cno
select * from @A
/*
A12020
A25070
A33010
*/
