不过可以在程序层实现的
伪代码:
start transacation
select 库存 from 库存表
if ($购买数量>库存)
echo “error”
else
insert 出库单 xxxxxx
update 库存
commit
create view kucunas
select 商品编号, sum(case when 是否有效=1 and *** 作类型=0 then 商品调动数量 when 是否有效=1 and *** 作类型=1 then -商品调动数量 else 0 end) as 数量,仓位 from table
我用的MYsql 具体函数有一点点出入:而且你的表名建的太刺激了,都是in、out、return等关键字,我在表名上都添加了一个数字1,即 in1,out1,back1,return1,transfer1:select
in1.id,
in1.amount -
if(in1.id in(select `inId` from out1), (select sum(out1.`amount`) from out1 where out1.inId=in1.id),0) +
if(in1.id in(select out1.`inId` from out1,back1 where out1.id=back1.outId),(select sum(back1.`amount`) from back1,out1 where out1.inId=in1.id and out1.id=back1.outId),0) -
if(in1.id in(select `inId` from transfer1),(select sum(transfer1.`amount`) from transfer1 where in1.id=transfer1.inId),0)-
if(in1.id in(select `inId` from return1), (select sum(return1.`amount`) from return1 where return1.inId=in1.id),0) as total
from in1
经过测试后结果为:
id, total
1.490
2.486
3.489
希望能对你有所帮助
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)