22,210
社区成员
发帖
与我相关
我的任务
分享求一存储过程
有数据表如下：
需求一个存储过程：
注意:此存储过程为先进先出计价存储过程.
(与 http://topic.csdn.net/u/20081012/00/5D6DE61B-8E9F-46FB-B4B5-E5B39D5F972E.html 贴有些相似,希望大家帮忙)
商品基本表
-------------------------------------
商品编码 商品名称 供应商编码 基准进价
-------------------------------------
0001 心相印纸巾 0010 13.6
0002 烟灰缸 0011 6.8
0003 一次性纸杯 0015 5.1
进货表
商品编码 进货价 进货数量 进货日期
-----------------------------------
0001 13.5 100 2008-5-1 10:05:01
0002 6.9 80 2008-5-1 10:05:01
0001 13.7 150 2008-5-15 16:01:02
0001 13.8 120 2008-5-25 14:15:05
销售价格明细表
商品编码 销售单价 销售数量 成本 销售日期
需求一个存储过程：
/*
第一次在执行 exec pro('0001',17.5,5,'2008-4-30 10:05:01')
在销售价格明细表中插入记录
商品编码 销售单价 销售数量 成本 销售日期
-------------------------------------------
0001 17.5 5 13.6 2008-4-30 10:05:01
第二次在执行 exec pro('0001',17.5,90,'2008-5-2 11:01:05')
在销售价格明细表中插入记录
商品编码 销售单价 销售数量 成本 销售日期
-----------------------------------------------------
0001 17.5 90 13.5 2008-5-2 11:01:05
第三次在执行 exec pro('0001',17.9,20,'2008-05-17 15:46:13')
在销售价格明细表中插入记录
商品编码 销售单价 销售数量 成本 销售日期
-------------------------------------------
0001 17.9 10 13.5 2008-05-17 15:46:13
0001 17.9 10 13.7 2008-05-17 15:46:13
*/
注意:此存储过程为先进先出计价存储过程.
(与 http://topic.csdn.net/u/20081012/00/5D6DE61B-8E9F-46FB-B4B5-E5B39D5F972E.html 贴有些相似,希望大家帮忙)
...全文
请发表友善的回复…
发表回复
喜-喜 2011-03-30
- 打赏
- 举报
勇顶 4 楼...
水向东流时光怎么偷 2011-03-30
- 打赏
- 举报
快溜 2011-03-30
- 打赏
- 举报
create table tb1(no char(10),jinhuojia numeric(18,2),jinhuonum int,jinhuodate datetime)
insert into tb1
select '0001',13.5,100,'2008-5-1 10:05:01' union all
select '0002',13.5,80,'2008-5-1 10:05:01' union all
select '0001',13.7,150,'2008-5-15 16:01:02' union all
select '0001',13.8,120,'2008-5-25 14:15:05'
create table tb2(no char(10),xiaoshoujia numeric(18,2),xiaoshounum int,chengben numeric(18,2),xiaoshoudate datetime)
create proc pr_name(@no char(10),@xiaoshoujia numeric(18,2),@xiaoshounum int,@xiaoshoudate datetime)
as
begin
declare @chengben numeric(18,2),@num int,@jinhuodate datetime
declare cursor1 cursor for
select jinhuojia,jinhuonum,jinhuodate from tb1 where no=@no and jinhuonum>0 order by jinhuodate
open cursor1
fetch next from cursor1 into @chengben,@num,@jinhuodate
while @@fetch_status=0
begin
if(@xiaoshounum<=@num and @xiaoshounum >0)
begin
insert into tb2 select @no,@xiaoshoujia,@xiaoshounum,@chengben,@xiaoshoudate
update tb1 set jinhuonum=@num-@xiaoshounum where no=@no and jinhuodate=@jinhuodate
set @xiaoshounum=0
end
else
begin
if(@xiaoshounum>0)
begin
insert into tb2 select @no,@xiaoshoujia,@num,@chengben,@xiaoshoudate
update tb1 set jinhuonum=0 where no=@no and jinhuodate=@jinhuodate
-- else
-- begin
-- insert into tb2 select @no,@xiaoshoujia,@xiaoshounum,@chengben,@xiaoshoudate
--update tb1 set jinhuonum=@num-@xiaoshounum where no=@no and jinhuodate=@jinhuodate
-- end
set @xiaoshounum=@xiaoshounum-@num
end
end
fetch next from cursor1 into @chengben,@num,@jinhuodate
end
close cursor1
deallocate cursor1
end
exec pr_name '0001',17.5,5,'2008-4-30 10:05:01'
select * from tb2
/*
no xiaoshoujia xiaoshounum chengben xiaoshoudate
---------- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.50 5 13.50 2008-04-30 10:05:01.000 */
exec pr_name '0001',17.5,100,'2008-4-30 10:05:01'
select * from tb2
/*
no xiaoshoujia xiaoshounum chengben xiaoshoudate
---------- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.50 5 13.50 2008-04-30 10:05:01.000
0001 17.50 95 13.50 2008-04-30 10:05:01.000
0001 17.50 5 13.70 2008-04-30 10:05:01.000 */
exec pr_name '0001',17.5,50,'2008-4-30 10:05:01'
select * from tb2
/*
no xiaoshoujia xiaoshounum chengben xiaoshoudate
---------- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.50 5 13.50 2008-04-30 10:05:01.000
0001 17.50 95 13.50 2008-04-30 10:05:01.000
0001 17.50 5 13.70 2008-04-30 10:05:01.000
0001 17.50 50 13.70 2008-04-30 10:05:01.000*/
exec pr_name '0001',17.5,60,'2008-4-30 10:05:01'
select * from tb2
/*
no xiaoshoujia xiaoshounum chengben xiaoshoudate
---------- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.50 5 13.50 2008-04-30 10:05:01.000
0001 17.50 95 13.50 2008-04-30 10:05:01.000
0001 17.50 5 13.70 2008-04-30 10:05:01.000
0001 17.50 50 13.70 2008-04-30 10:05:01.000
0001 17.50 60 13.70 2008-04-30 10:05:01.000*/
exec pr_name '0001',17.5,60,'2008-4-30 10:05:01'
select * from tb2
/*
no xiaoshoujia xiaoshounum chengben xiaoshoudate
---------- --------------------------------------- ----------- --------------------------------------- -----------------------
0001 17.50 5 13.50 2008-04-30 10:05:01.000
0001 17.50 95 13.50 2008-04-30 10:05:01.000
0001 17.50 5 13.70 2008-04-30 10:05:01.000
0001 17.50 50 13.70 2008-04-30 10:05:01.000
0001 17.50 60 13.70 2008-04-30 10:05:01.000
0001 17.50 35 13.70 2008-04-30 10:05:01.000
0001 17.50 25 13.80 2008-04-30 10:05:01.000*/
select * from tb1
/*
no jinhuojia jinhuonum jinhuodate
---------- --------------------------------------- ----------- -----------------------
0001 13.50 0 2008-05-01 10:05:01.000
0002 13.50 80 2008-05-01 10:05:01.000
0001 13.70 0 2008-05-15 16:01:02.000
0001 13.80 95 2008-05-25 14:15:05.000
gw6328 2011-03-30
- 打赏
- 举报
用参数区分，或者用触发器，如果能处理子表那种情况。
xiaoping04408 2011-03-30
- 打赏
- 举报
先进先出，你可以把进货表按时间排一下顺序。循环取数吧
