现在有三个表
1. 库存表 (库存编号,产品编号,库存数量)
2. 采购单表(采购单编号,采购时间)
3. 采购单明细表(明细编号,采购单编号,产品编号,采购数量,过期时间)
假设出库的时候，都是先出 过期时间最早的，这样的话，能够根据 库存数量、采购明细 倒推出现有库存中最早的过期时间。 我现在想查询 30天后过期的 产品编号、库存数量、过期时间 ，请问sql语句或者存储过程改如何写,数据库用的sql server 2008, 谢谢！
------解决方案--------------------
--应该是getdate() 我没有测试，直接写的
select t1.产品编号,t1.过期时间,sum(t2.库存数量) as 库存数量
from 采购单明细表 t1 (nolock)
inner join 库存表 t2 (nolock) on t1.产品编号=t2.产品编号
where convert(varchar(10),t1.过期时间,120)>=convert(varchar(10),getdate()+30,120)
group by t1.产品编号,t1.过期时间
------解决方案--------------------
来一个最笨的办法
游标遍历
declare @cpbh int
declare @kc decimal(18,3)
declare c1 cursor for select 产品编号,库存数量 from dbo.库存表
--where 产品编号=1
open c1
fetch next from c1 into @cpbh,@kc
while @@fetch_status=0
begin
declare @ljkc decimal(18,2) --累计库存
set @ljkc=0
declare @cgmxid int
declare @gqsj datetime
set @gqsj='6000-01-01'
while(@ljkc<@kc)
begin
select @ljkc=@ljkc+ a1.采购数量 ,@gqsj=a1.过期时间 from
采购单明细表 a1 where 产品编号=@cpbh
and a1.过期时间<@gqsj
and not exists( select 明细编号 from 采购单明细表 b1 where a1.产品编号=b1.产品编号
and b1.过期时间<@gqsj
and b1.过期时间>a1.过期时间)
print @kc
print @ljkc
print @gqsj
end
update dbo.库存表
set 过期时间=@gqsj
where 产品编号=@cpbh
fetch next from c1 into @cpbh,@kc
end
close c1
deallocate c1
------解决方案--------------------
来个sql 递归
with tb(明细编号,产品编号 ,采购数量 ,过期时间 )
as (
select a1.明细编号,a1.产品编号 ,a1.采购数量 ,a1.过期时间 from
采购单明细表 a1 where not exists( select 明细编号 from 采购单明细表 b1 where a1.产品编号=b1.产品编号
and b1.过期时间>a1.过期时间)
union all
select a1.明细编号,a1.产品编号 ,cast( a1.采购数量 +tb.采购数量 as decimal(18,3) ) ,a1.过期时间
from 采购单明细表 a1 inner join tb
on a1.产品编号=tb.产品编号
and ( (a1.过期时间<tb.过期时间 ) or (a1.过期时间=tb.过期时间 and a1.明细编号<tb.明细编号) )
and not exists (select 明细编号 from 采购单明细表 b1 where b1.产品编号=a1.产品编号
and ( (b1.过期时间<tb.过期时间 ) or (b1.过期时间=tb.过期时间 and b1.明细编号<tb.明细编号) )
and ( (b1.过期时间>a1.过期时间 ) or (b1.过期时间=a1.过期时间 and b1.明细编号>a1.明细编号) )
)
)
update dbo.库存表
set 过期时间=(
select top 1 过期时间 from tb b
where 库存表.产品编号=b.产品编号
and 库存表.库存数量<=b.采购数量
order by b.过期时间 desc ,b.明细编号 desc
)
------解决方案--------------------
with
t2 as
(
select b.*,row_number() over(order by 采购时间 desc ) re from [采购单表] a join [采购单明细表] b
on a.采购单编号=b.采购单编号
)
,t3 as
(
select t2.*,(select sum(采购数量) from t2 a where 产品编号=t2.产品编号 and re<=t2.re) 累计
from t2
)
select * into #tt1 from t3
select a.*,累计-库存数量 as 累计2,库存数量 into #tt2
from #tt1 a join [库存表] b on a.产品编号=b.产品编号
select a.产品编号,MIN(累计2) as min累计 into #tt3
from #tt2 a
where 累计2>=0
group by a.产品编号
select a.产品编号,min(a.过期时间) as 最早过期时间 into #tt4
from #tt2 a join #tt3 b
on a.产品编号=b.产品编号 and a.累计2<=b.min累计
group by a.产品编号
update a set a.过期时间=b.最早过期时间 from [库存表] a join #tt4 b on a.产品编号=b.产品编号
select * from [库存表]
drop table #tt1
drop table #tt2
drop table #tt3
------解决方案--------------------
这样数据库设计会让报表执行很慢的，因为每次出报表都要重新计算采购库存和销售的和差问题；在库存上可以设计成记录【批次库存】同样有效期的商品放在一起，就是说在批次库存表里记录着的商品会出现多次（即多个批次）；然后销售的时候按照批次进行销售，销售那边的代码也简单了不少，不然销售的时候还是需要判断哪些是需要先出的（先进先出原则）。