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等等.

Answer 2

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等等.