Product(pid,name,amount,nowAmount):标识,名称,已有数量,当前数量
Trade(id,pid,operType,number):标识,产品标识,操作类型(入库:1,出库:0),数量
Product表中测试数据:
1 | 苹果 | 100 | 0 |
2 | 桔子 | 50 | 0 |
1 | 1 | 1 | 432 |
2 | 1 | 0 | 50 |
3 | 2 | 1 | 20 |
4 | 2 | 0 | 40 |
5 | 1 | 1 | 30 |
6 | 2 | 0 | 20 |
现要求一条SQL语句更新Product表中nowMount值
语句如下:
update
p
set
p.nowAmount
=
p.amount
+
t.
number
from Product as p ,( select pid, sum ( case operType when ' 0 ' then number * ( - 1 ) else number end ) number
from Trade group by pid) as t where t.pid = s.pid
或者
from Product as p ,( select pid, sum ( case operType when ' 0 ' then number * ( - 1 ) else number end ) number
from Trade group by pid) as t where t.pid = s.pid
update
p
set p.nowAmount = t. number + p.amount
from Product as p
inner join ( select pid , sum ( case operType when ' 0 ' then number * ( - 1 ) else number end ) as number from Trade group by pid) as t
on p.pid = t.pid
两者一样执行后Product表:
set p.nowAmount = t. number + p.amount
from Product as p
inner join ( select pid , sum ( case operType when ' 0 ' then number * ( - 1 ) else number end ) as number from Trade group by pid) as t
on p.pid = t.pid
1 | 苹果 | 100 | 512 |
2 | 桔子 | 50 | 10 |