请教各位大虾关于先进先出成本问题
请教各位大虾,我有一个明细表mingx
id 商品代码 数量 价格 日期 类别
1 001 10 8 2007-03-01 采购
2 001 8 12 2007-03-10 采购
3 001 12 6 2007-04-15 采购
4 001 2 16 2007-04-05 销售
5 001 18 16 2007-04-20 销售
我的目的是按照先进先出方法统计4月份销售数量和销售成本，销售成本应该是10*8元
+8*12元+2*6元=188元，销售数量为20个,请问各位大虾怎么写sql语句
[解决办法]
--好了~~改完了
create table mingx
(id int, 商品代码 varchar(5), 数量 int, 价格 int, 日期 datetime, 类别 varchar(10))
insert into mingx select 1, '001 ', 10, 8, '2007-03-01 ', '采购 '
union all select 2, '001 ', 8, 12, '2007-03-10 ', '采购 '
union all select 3, '001 ', 12, 6, '2007-04-15 ', '采购 '
union all select 4, '001 ', 12, 6, '2007-04-15 ', '采购 '
union all select 5, '001 ', 2, 16, '2007-04-05 ', '销售 '
union all select 6, '001 ', 18, 16, '2007-04-20 ', '销售 '
union all select 7, '001 ', 2, 10, '2007-05-15 ', '销售 '
union all select 8, '002 ', 12, 6, '2007-04-15 ', '采购 '
union all select 9, '002 ', 12, 6, '2007-04-15 ', '销售 '
union all select 10, '003 ', 12, 6, '2007-03-15 ', '采购 '
union all select 11, '003 ', 12, 6, '2007-04-15 ', '采购 '
union all select 12, '003 ', 10, 16, '2007-05-15 ', '销售 '
select a.商品代码,
sum(case when a.数量sum <=b.数量 then a.数量*a.价格
when(a.数量sum-b.数量) <a.数量 then (a.数量+b.数量-a.数量sum)*a.价格
else 0 end)[成本],
max(b.数量)[数量]
from
(select a.id,a.商品代码,
(case when a.数量sum-b.数量 <=0 then 0 when a.数量sum-b.数量 <=a.数量 then a.数量sum-b.数量 else a.数量 end)[数量],
价格,日期,isnull((case when a.数量sum-b.数量 <0 then 0 else a.数量sum-b.数量 end),a.数量sum)[数量sum]
from
(select *,(select sum(数量) from mingx b where b.商品代码=a.商品代码 and b.id <=a.id)[数量sum] from mingx a where 类别= '采购 ')a
left join
(select 商品代码,sum(数量)[数量] from mingx where 类别= '销售 ' and convert(varchar(7),日期,21) < '2007-05 ' group by 商品代码)b
on b.商品代码=a.商品代码) a
join
(select 商品代码,sum(数量)[数量] from mingx where 类别= '销售 ' and convert(varchar(7),日期,21)= '2007-05 ' group by 商品代码)b
on b.商品代码=a.商品代码
group by a.商品代码
商品代码 成本 数量
----- ----------- -----------
001 12 2
003 60 10
（所影响的行数为 2 行）
[解决办法]
declare @mingx table
(id int, 商品代码 varchar(5), 数量 int, 价格 int, 日期 datetime, 类别 varchar(10))
insert into @mingx select 1, '001 ', 10, 8, '2007-03-01 ', '采购 '
union all select 2, '001 ', 8, 12, '2007-03-10 ', '采购 '
union all select 3, '001 ', 12, 6, '2007-04-15 ', '采购 '
union all select 4, '001 ', 12, 6, '2007-04-15 ', '采购 '
union all select 5, '001 ', 2, 16, '2007-04-05 ', '销售 '
union all select 6, '001 ', 18, 16, '2007-04-20 ', '销售 '
union all select 7, '001 ', 2, 10, '2007-05-15 ', '销售 '
union all select 8, '002 ', 12, 6, '2007-04-15 ', '采购 '
union all select 9, '002 ', 12, 6, '2007-04-15 ', '销售 '
union all select 10, '003 ', 12, 6, '2007-03-15 ', '采购 '
union all select 11, '003 ', 12, 6, '2007-04-15 ', '采购 '
union all select 12, '003 ', 10, 16, '2007-05-15 ', '销售 '
select tmp2.商品代码,
[日期]=convert(varchar(6),tmp2.日期,112),
[成本金额]=sum((case when sum_销售 <sum_采购 then sum_销售 else sum_采购 end
-
case when sum_销售-tmp2.数量 <sum_采购-tmp1.数量 then sum_采购-tmp1.数量 else sum_销售-tmp2.数量 end)
*tmp1.价格)
from
(select *,
sum_采购=(select sum(数量) from @mingx where 类别=t1.类别 and 商品代码=t1.商品代码 and id!> t1.id)
from @mingx t1 where 类别= '采购 ')tmp1
join
(select *,
sum_销售=(select sum(数量) from @mingx where 类别=t2.类别 and 商品代码=t2.商品代码 and id!> t2.id)
from @mingx t2 where 类别= '销售 ')tmp2
on
tmp1.商品代码=tmp2.商品代码
where sum_销售-tmp2.数量 <sum_采购 and sum_采购-tmp1.数量 <sum_销售
and convert(varchar(6),tmp2.日期,112)=200704--取4月
group by tmp2.商品代码,convert(varchar(6),tmp2.日期,112)
(12 行受影响)
商品代码 日期 成本金额
----- ------ -----------
001 200704 188
002 200704 72
(2 行受影响)
