表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(余下的數)
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(余下的數)
解决方案 »
- sql 查询问题
- SQL语句执行“字符串”,表达不清请见谅
- dataset选择行
- SQL 中 [全文索引] 与 [字典] 都是作什么的?
- sql server 2000 开发版, server 注册为什么不能使用混合模式,提示未与信任sqlserver 连接
- Server 对象, ASP 0177 (0x8007007F)
- 存储过程优化,排序开销大
- 请大家帮忙,waitfor控制影响
- ### KingSunSha(弱水三千)进来拿分,问题是:如何有效保护MDB数据库 ###
- SQL 无关联数据集一对多合并
- 这么简单的一个存储过程功能语法有误吗?帮我看一下,谢谢,散分
- where 筛选 如何删除。谢谢。
else b.num-(select sum(sNum) from t where t where a.cno=cno and seqno<=a.seqno)
end
from t a left join t1 b on a.cno=b.cno
(CNo varchar(10) ,SeqNo int, sNum int)
insert into t
select 'A', 1, 20 union all
select 'A', 2, 50 union all
select 'A', 3, 30create table t1
(CNo varchar(10), Num int)
insert into t1
select 'A', 80
select a.*,num=case when (select sum(sNum) from t where a.cno=cno and seqno<=a.seqno)<b.num then a.sNum
else b.num-(select sum(sNum) from t where a.cno=cno and seqno<=a.seqno)
end
from t a left join t1 b on a.cno=b.cno
CNo SeqNo sNum num
---------- ----------- ----------- -----------
A 1 20 20
A 2 50 50
A 3 30 -20(3 row(s) affected)
(CNo varchar(10) ,SeqNo int, sNum int)
insert into t
select 'A', 1, 20 union all
select 'A', 2, 50 union all
select 'A', 3, 30create table t1
(CNo varchar(10), Num int)
insert into t1
select 'A', 80
select a.*,num=case when (select sum(sNum) from t where a.cno=cno and seqno<=a.seqno)<b.num then a.sNum
else b.num-(select sum(sNum) from t where a.cno=cno and seqno<a.seqno)
end
from t a left join t1 b on a.cno=b.cno
CNo SeqNo sNum num
---------- ----------- ----------- -----------
A 1 20 20
A 2 50 50
A 3 30 10(3 row(s) affected)
insert into 表A select 'A', 1, 20,null
insert into 表A select 'A', 2, 50,null
insert into 表A select 'A', 3, 30,nullcreate 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.CNoselect *
from 表Adrop table 表A,表B
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试试.
budong0000(Be objective about objects)的方法語法錯誤
fa_ge(鶴嘯九天)的方法還沒測試好,接著再測
insert @A
select 'A', 1,20,null union all
select 'A', 2,50,null union all
select 'A', 3,30,nulldeclare @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
/*
A 1 20 20
A 2 50 70
A 3 30 10
*/--这是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
/*
A 1 20 20
A 2 50 70
A 3 30 10
*/
我所列舉的數據確實是比較的特殊的,實際應用中不會是這麼有規律的,比如SeqNo就不會是連續的,不知會不會有問題?
但是上述算法还有待改进。
改變B表中的Num為10、20、30等等試一下,會出現一些不合要求的結果
select a.*, dNum =
case
when sNum<=B.Num-isnull((select sum(sNum) from 表A where CNo=a.CNo and SeqNo<a.SeqNo),0) then sNum
else case when B.Num-isnull(((select sum(sNum) from 表A where CNo=a.CNo and SeqNo<a.SeqNo)),0)<0 then 0 else B.Num-isnull(((select sum(sNum) from 表A where CNo=a.CNo and SeqNo<a.SeqNo)),0) end
end
from 表A as a
inner join 表B as b on a.CNo=B.CNo
這樣不知對麼
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,nullcreate table 表B (CNo varchar(100), Num int)
insert into 表B select 'A', 20
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 case when B.Num-isnull((select sum(sNum) from 表A where CNo=a.CNo and SeqNo<a.SeqNo),0)<0 then 0 else B.Num-isnull((select sum(sNum) from 表A where CNo=a.CNo and SeqNo<a.SeqNo),0) end
end
from 表A as a
inner join 表B as b on a.CNo=B.CNo
select *
from 表A as a drop table 表A,表B