请教大虾关于取得先进先出商品的进价的问题

如不同日期同一种商品的进货记录

商品代码，进价，数量，进货日期
001   8   10   2007-04-10
001   10   20   2007-04-12
001   12   30   2007-04-15
002   5   10   2007-01-15

当001该商品第一次卖出4个时，我要取道8元的进价，第二次卖出8个的话，我要取道10元的进价，如果第三次卖出25个话，前面已经卖出12个，也就是25个中18个的进价应是10元，7个应是12元，请问以上sql语句怎么写，万分感激



------解决方案--------------------
CREATE TABLE [dbo].[frontfront](
[No] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Price] [int] NULL,
[Amount] [int] NULL,
[InDate] [datetime] NULL
) ON [PRIMARY]
go

insert into 先进先出 select '001 ',8, 10, '2007-04-10 '
union all select '001 ', 10, 20, '2007-04-12 '
union all select '001 ', 12, 30, '2007-04-15 '
union all select '002 ', 5, 10, '2007-01-15 '
go

create procedure frontfront_getPrice
(@no nvarchar(50),@amount int)
as
--declare @amount int
--declare @no int
--set @amount=25
--set @no=001
declare @date datetime
select top 1 @date=indate from frontfront f1 where [no]=@no
and (select sum(amount) from frontfront where [no]=@no and indate <=f1.indate)> @amount order by Indate asc
select [no],price,amount=(case indate when @date then @amount-(select sum(amount) from frontfront where [no]=@no and indate <@date) else amount end),inDate from frontfront where [no]=@no and indate <=@date


go
exec frontfront_getPrice '001 ',25

结果如下:
no price amount inDate
------------------------------- ----------- ----------- -----------------------
001 8 10 2007-04-10 00:00:00.000
001 10 15 2007-04-12 00:00:00.000

------解决方案--------------------
以下是先进先出
declare @a table(进货单 int ,供应商_id varchar(2),商品_id varchar(1),定价 decimal(9,3),进货数 int,
折扣 decimal(5,3),jh_time datetime)
insert into @a
select '1 ', 'A1 ', 'B ',10,100,0.6, '2006-01-01 'union all
select '2 ', 'A2 ', 'B ',10,50,0.7, '2006-02-01 'union all
select '3 ', 'A1 ', 'B ',10,100,0.6, '2006-03-01 '
--销售表
declare @b table (销售单_id varchar(2),商品_id varchar(1),定价 decimal(9,3),销售数量 int,
折扣 decimal(5,3),jh_time datetime)
insert into @b
select '11 ', 'B ',10,80,0.9, '2006-01-02 'union all
select '22 ', 'B ',10,60,0.8, '2006-02-02 'union all
select '33 ', 'B ',10,40,1, '2006-03-02 '

select 销售单_id,tb.商品_id,
[sales]=
sum(((case when tb.num> ta.num then ta.num else tb.num end)-
(case when ta.num-ta.进货数 <tb.num-tb.销售数量 then tb.num-tb.销售数量 else ta.num-ta.进货数 end))*ta.折扣)

from
(select *,
num=(select isnull(sum(进货数),0) from @a where 商品_id=a.商品_id and 进货单!> a.进货单)
from @a a)ta
inner join
(select *,
num=(select isnull(sum(销售数量),0) from @b where 商品_id=b.商品_id and 销售单_id!> b.销售单_id)
from @b b)tb
on ta.商品_id=tb.商品_id
where
tb.num-销售数量 <ta.num and ta.num-进货数 <tb.num
group by 销售单_id,tb.商品_id
order by 销售单_id

(3 行受影响)

(3 行受影响)

销售单_id 商品_id sales
------ ----- ---------------------------------------
11 B 48.000
22 B 40.000
33 B 25.000

(3 行受影响)