Question

用sql怎么实现库存的先进先出？

有两张表，表1（库存）：入库明细：货号、批次、数量，表2（销售）：货号、数量
请问怎么用先进先出的算法，算出每个批次剩余库存数，用sql编写。
例如：表1： 001 1 100 表2：001 400
001 2 200
001 3 300
结果应该是 001 1 0
001 2 0
001 3 200

请问这个用sql怎么编写

Answer 1

create table 表1(货号 varchar(20),批次 int ,数量 int)

create table 表2(货号 varchar(20) ,数量 int)
/*------------------------------*/
insert into 表1
select '001', 1, 100 union all
select '001', 2, 200 union all
select '001', 3, 300

/*------------------------------*/
insert into 表2
select '001', 400
/*------------------------------*/
select * from 表1

select * from 表2
/*------------------------------*/
select t1.货号,t1.批次,
case when ((select isnull(sum(数量),0) from 表1 t3 where t3.货号=t1.货号 and t3.批次<t1.批次)-isnull(t2.new_数量,0))<0
then case when ((select sum(数量) from 表1 t4 where t4.货号=t1.货号 and t4.批次<=t1.批次)-isnull(t2.new_数量,0))<0 then 0
else ((select sum(数量) from 表1 t4 where t4.货号=t1.货号 and t4.批次<=t1.批次)-isnull(t2.new_数量,0))
end
else t1.数量
end as 批次剩余库存数
from 表1 t1
left join (select 货号,sum(数量) as new_数量 from 表2 group by 货号) t2
on t1.货号=t2.货号
/*------------------------------*/

PS:上面将null转换为0的函数为isnull,是SQL SERVER下的函数,你只要根据你的数据转换为对应函数就可以了,如oracle是nvl,mysql是ifnull等等.

有问题hi我

Answer 2

要用SQL做的话，我就想到用存储过程了。
我的思路如下：
两层循环搞定
外层循环发货表，取出一对pno、num；
内层循环对每一个pno、num对应的进货表做减值操作，更新进货表相应的数据。首先找到进货表中与pno对应的批次(inno)存入一个LIST中，再依次对进货表中的每一对pon、inno进行计算，并更新进货表。

内层循环做计算：
if (ordernum-curnum)>0 then
update classsource set num = 0 where pno=orderpno and inno = n;
let ordernum =ordernum - curnum;
continue foreach;
else
update classsource set num = (curnum - ordernum) where pno=orderpno and inno = n;
exit foreach;
end if;

详细代码参看参考资料
我的数据库是informix， 有返回值的叫function而不是procedure。
所以你看到的是funcion.

Answer 3

--先计算每行的sum数量,然后找出比表2的数量多的最小批次批次,然后比该最小批次的批次号还小的批次全部改成0,临界的那个批次显示为之前的每行sum数量减去原表数量
select a.货号,a.批次,case when (合计 - b.数量) <=0 then 0 when (合计 - b.数量) < a.数量 then (合计 - b.数量) else a.数量 end as 剩余数量 from(
select *,合计=(select sum(数量) from @tb b where b.批次 <= a.批次)
from 入库明细 a
) a left join (select 货号,sum(数量) as 数量 from 销售 group by 货号) b

Answer 4

select n.货号,n.批次,
case
when n.countnum - l.数量< 0 then 0
when n.countnum - l.数量> m.数量 then m.数量
else n.countnum - l.数量 end as 剩余
from 库存 m,
(select a.货号,a.批次,sum(b.数量) as countnum
from 库存 a,库存 b
where a.货号 = b.货号 and a.批次 >= b.批次
group by a.货号,a.批次 ) n,销售 l
where m.货号 = n.货号 and m.批次 = n.批次 and n.货号 = l.货号

Answer 5

可以用存储实现~