create table Test
(
Style varchar(20),--样式
Color varchar(20),--颜色
Size varchar(20),--尺寸
Price decimal(18,2),--价格
Quantity int,--库存
InDate datetime--入库时间
)
GO
insert into Test values('A','红色','L',100,100,'2018-01-01 00:00:00.000')
insert into Test values('B','蓝色','L',50,50,'2018-01-02 00:00:00.000')
insert into Test values('A','红色','L',80,50,'2018-01-03 00:00:00.000')
insert into Test values('A','蓝色','S',100,100,'2018-01-04 00:00:00.000')
insert into Test values('C','红色','M',10,60,'2018-01-05 00:00:00.000')
insert into Test values('A','红色','L',111,1,'2018-01-06 00:00:00.000')
GO
select Style,Color,Size,Price,Quantity,InDate from Test
/*********************************************************
上表记录的是衣服各款不同时间点得进货记录。
包含进货价格Price,数量Quantity,以及进货时间InDate
1、根据 Style、Color、Size 能确定一款衣服
2、按照先进先出得原则,编写一个存储过程,能够得出出售某款衣服不同数量得平均成本价是多少?
例如:出售 A类红色L尺码 110件,需要先从 2018-01-01 取100件,再从2018-01-03中取10件。
平均成本价: (100*100 + 10*80) / 110
**********************************************************/
go
create proc proc_avg
@style varchar(20),--样式
@color varchar(20),--颜色
@size varchar(20),--尺寸
@number int,--数量
@AvgMoney decimal(18,2) out--平均成本价
as
begin
declare @price decimal(18,2)--价格
declare @quantity int--数量
declare @nowNumber int--现在的数量
declare @SumMoney decimal(18,2)--总金额
SET @SumMoney=0;
set @nownumber=@number
--定义游标
declare shut_cursor cursor scroll dynamic
for SELECT price,quantity
FROM Test where style=@style and color=@color and size=@size order by indate --给游标赋值
open shut_cursor--打开游标
fetch next from shut_cursor into @price,@quantity--读取第一行数据
while @@FETCH_STATUS=0 --游标读取下一条数据是否成功
begin
if @nownumber-@quantity > 0--判断数量是否足够
begin
set @nowNumber=@nowNumber-@quantity;
set @SumMoney=@price*@quantity+@SumMoney
fetch next from shut_cursor into @price,@quantity--读取下一行数据
end
else
begin
set @SumMoney=@price*@nowNumber+@SumMoney
set @AvgMoney=cast( @SumMoney/@number as decimal(18,2))
break
end
end
close shut_cursor --关闭游标
deallocate shut_cursor--删除游标,释放资源
end
go
--执行
declare @AvgMoney decimal(18,2)
exec proc_avg 'A','红色','L',110,@AvgMoney out
print @AvgMoney