求进销存中月底（或任意时间）各供应商库存数量的算法-按先进先出

Question

入库日期      供应商    采购数量
2010-1-1   a         5
2010-1-2   b         2
2010-1-3   a         3
出库日期 出库数量
2010-1-4  6
2010-2-2  2求月底按供应商列出库存数量-按先进先出
库存余额表2010-1-31 a 3
2010-1-31 b 1

Answer 1

你的结果貌似有问题
if not object_id('入库表') is null
    drop table 入库表
go
create table 入库表
(
  入库日期 datetime,
  供应商 varchar(20),
  采购数量 int
)
insert into 入库表 select '2010-1-1','a',5
union all select '2010-1-2','b',2
union all select '2010-1-3','a',3
goif not object_id('出库表') is null
    drop table 出库表
go
create table 出库表
(
  出库日期 datetime,
  供应商 varchar(20),
  出库数量 int
)
insert into 出库表 select '2010-1-4','a',6
union all select '2010-2-2','b',2goselect r.供应商,sum(入库数量)-isnull(sum(c.出库数量),0) as 余额表
from
(
 select 供应商,sum(采购数量) 入库数量 from 入库表
group by 供应商
) r
join
(
 select 供应商,sum(出库数量) 出库数量 from 出库表
group by 供应商
) c
on r.供应商=c.供应商
group by  r.供应商供应商                  余额表
-------------------- -----------
a                    2
b                    0(2 行受影响)

Answer 2

--> 测试数据：#i
if object_id('tempdb.dbo.#i') is not null drop table #i
create table #i(入库日期 datetime, 供应商 varchar(8), 采购数量 int)
insert into #i
select '2010-1-1', 'a', 5 union all
select '2010-1-2', 'b', 2 union all
select '2010-1-3', 'a', 3
--> 测试数据：#o
if object_id('tempdb.dbo.#o') is not null drop table #o
create table #o(出库日期 datetime, 出库数量 int)
insert into #o
select '2010-1-4', 6 union all
select '2010-2-2', 2declare @out int
select @out = sum(出库数量) from #o where 出库日期 < '2010-02-01';with cte as
(
    select *, si = (select sum(采购数量) from #i where 入库日期<=t.入库日期) from #i t where 入库日期 < '2010-02-01'
)
select 结算日 = '2010-01-31', 供应商,
    结余 = case when si-采购数量<@out then si-@out else 采购数量 end
from cte where si > @out/*
结算日     供应商   结余
---------- -------- -----------
2010-01-31 b        1
2010-01-31 a        3
*/

Answer 3

if object_id('ins')>0
drop table ins
create table ins
(
入库日期 datetime,
  供应商 varchar(20),
  采购数量 int
)if object_id('out')>0
drop table out
create table out
(
出库日期 datetime,
  供应商 varchar(20),
  出库数量 int)insert into ins
select '2010-1-1','a',5
union all 
select '2010-1-2','b',2
union all 
select '2010-1-3','a',3
insert into out
select '2010-1-4','a',6
union all 
select '2010-2-2','b',2select * from ins
select * from outselect t.日期 as 月份,t.供应商,sum(t.数量)
from 
(
select month(入库日期) as 日期,供应商,sum(采购数量) as 数量
from ins
group by 供应商,month(入库日期)
union all
select month(出库日期),供应商,sum(出库数量)*(-1) as 数量
from out
group by 供应商,month(出库日期)
) t
group by t.日期,t.供应商
--结果--你给出的结果有问题。
1 a 2
1 b 2
2 b -2

Answer 4

楼上SQLCenter的结果是对的，他才是真正理解了我的意思，但是SQLCenter的sql语句我不能执行，我用的是sqlserver2000，执行是提示‘在关键字 'with' 附近有语法错误’

Answer 5

if object_id('tempdb.dbo.#i') is not null drop table #i
create table #i(入库日期 datetime, 供应商 varchar(8), 采购数量 int)
insert into #i
select '2010-1-1', 'a', 5 union all
select '2010-1-2', 'b', 2 union all
select '2010-1-3', 'a', 3
--> 测试数据：#o
if object_id('tempdb.dbo.#o') is not null drop table #o
create table #o(出库日期 datetime, 出库数量 int)
insert into #o
select '2010-1-4', 6 union all
select '2010-2-2', 2declare @out int
select @out = sum(出库数量) from #o where 出库日期 < '2010-02-01';select * into cte from
(
select *, si = (select sum(采购数量) from #i where 入库日期<=t.入库日期) from #i t where 入库日期 < '2010-02-01'
) tt
select 结算日 = '2010-01-31', 供应商,
    结余 = case when si-采购数量<@out then si-@out else 采购数量 end
from cte where si > @out