现在有一个商品要出库，肯定和某些入库记录（可能一条，可能多条）相关，你得找出和哪几条入库记录（同一个商品）相关，出库时按照先进先出的原则处理入库记录（和出库相同的商品）下面是本坛中查找到的(ROY_88)编写的先进先出的代码，--(Roy)生成
if not object_id('进货表') is null
drop table 进货表
Go
Create table 进货表([商品编码] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime) -- [进货批次]
Insert 进货表
select N'0001',13.5,100,'2008-5-1 10:05:01' union all --A
select N'0002',6.9,80,'2008-5-1 10:05:01' union all --B
select N'0001',13.7,150,'2008-5-15 16:01:02' union all --C
select N'0002',6,800,'2008-5-18 11:05:01' union all --D
select N'0001',13.8,120,'2008-5-25 14:15:05' --E
Go
--> --> (Roy)生成測試數據
if not object_id('销售表') is null
drop table 销售表
Go
Create table 销售表([商品编码] nvarchar(4),[销售单价] decimal(18,1),[销售数量] int,[成本] nvarchar(1),[销售日期] Datetime,) --[进货批次]
Insert 销售表
select N'0001',17.5,110,N'?','2008-5-12 11:01:05' union all
select N'0001',16.5,110,N'?','2008-5-2 11:01:05' union all
select N'0002',10,120,N'?','2008-5-2 11:01:05' union all
select N'0002',17.9,40,N'?','2008-5-17 15:46:13'
Goselect
s.[商品编码],s.[销售单价],s.[销售数量],
[成本]=sum(p.[进货价]*
(case when p.[Sum_进货]>s.[Sum_销售] then s.[Sum_销售] else p.[Sum_进货] end
-case when s.[Sum_销售]-s.[销售数量]>p.[Sum_进货]-p.[进货数量] then s.[Sum_销售]-s.[销售数量] else p.[Sum_进货]-p.[进货数量] end)),
s.[销售日期] from
(select t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期],sum(t2.[进货数量]) as [Sum_进货] from 进货表 t1 join 进货表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[进货日期]>=t2.[进货日期] group by t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期])p
join
(select t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期],sum(t2.[销售数量]) as [Sum_销售] from 销售表 t1 join 销售表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[销售日期]>=t2.[销售日期] group by t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期])s
on p.[商品编码]=s.[商品编码]
where
p.[Sum_进货]>s.[Sum_销售]-s.[销售数量] and s.[Sum_销售]>p.[Sum_进货]-p.[进货数量]
group by s.[商品编码],s.[销售单价],s.[销售数量],s.[销售日期]
想达到类似的效果商品编码 销售单价 销售数量 成本 销售日期 进货批次(如果是一次出的数量大于最近采购的数量，则需要拆分)
---- ------------------ ----------- --------------------- -----------------------
0001 17.5 100 1315 2008-05-02 11:01:05.000 A
0001 17.5 10(110-100) 137 2008-05-02 11:01:05.000 C
------
if not object_id('进货表') is null
drop table 进货表
Go
Create table 进货表([商品编码] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime) -- [进货批次]
Insert 进货表
select N'0001',13.5,100,'2008-5-1 10:05:01' union all --A
select N'0002',6.9,80,'2008-5-1 10:05:01' union all --B
select N'0001',13.7,150,'2008-5-15 16:01:02' union all --C
select N'0002',6,800,'2008-5-18 11:05:01' union all --D
select N'0001',13.8,120,'2008-5-25 14:15:05' --E
Go
--> --> (Roy)生成測試數據
if not object_id('销售表') is null
drop table 销售表
Go
Create table 销售表([商品编码] nvarchar(4),[销售单价] decimal(18,1),[销售数量] int,[成本] nvarchar(1),[销售日期] Datetime,) --[进货批次]
Insert 销售表
select N'0001',17.5,110,N'?','2008-5-12 11:01:05' union all
select N'0001',16.5,110,N'?','2008-5-2 11:01:05' union all
select N'0002',10,120,N'?','2008-5-2 11:01:05' union all
select N'0002',17.9,40,N'?','2008-5-17 15:46:13'
Goselect
s.[商品编码],s.[销售单价],s.[销售数量],
[成本]=sum(p.[进货价]*
(case when p.[Sum_进货]>s.[Sum_销售] then s.[Sum_销售] else p.[Sum_进货] end
-case when s.[Sum_销售]-s.[销售数量]>p.[Sum_进货]-p.[进货数量] then s.[Sum_销售]-s.[销售数量] else p.[Sum_进货]-p.[进货数量] end)),
s.[销售日期] from
(select t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期],sum(t2.[进货数量]) as [Sum_进货] from 进货表 t1 join 进货表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[进货日期]>=t2.[进货日期] group by t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期])p
join
(select t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期],sum(t2.[销售数量]) as [Sum_销售] from 销售表 t1 join 销售表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[销售日期]>=t2.[销售日期] group by t1.[商品编码],t1.[销售单价],t1.[销售数量],t1.[成本],t1.[销售日期])s
on p.[商品编码]=s.[商品编码]
where
p.[Sum_进货]>s.[Sum_销售]-s.[销售数量] and s.[Sum_销售]>p.[Sum_进货]-p.[进货数量]
group by s.[商品编码],s.[销售单价],s.[销售数量],s.[销售日期]
想达到类似的效果商品编码 销售单价 销售数量 成本 销售日期 进货批次(如果是一次出的数量大于最近采购的数量，则需要拆分)
---- ------------------ ----------- --------------------- -----------------------
0001 17.5 100 1315 2008-05-02 11:01:05.000 A
0001 17.5 10(110-100) 137 2008-05-02 11:01:05.000 C
------
--问题
进货表结构： 进货编号 品名 单价 进货数
----------- ---------- --------------------- -----------
1001 aa 500.0000 5
1002 aa 400.0000 10
1002 bb 200.0000 2
1003 aa 500.0000 20
1004 bb 300.0000 20 出货表结构： 出货编号 品名 出货数
----------- ---------- -----------
2001 aa 11
2001 bb 10
2001 aa 20
2001 aa 1 结果表： 出货编号 品名 出货数 单价
----------- ---------- -----------
2001 aa 5 500.0000
2001 aa 6 400.0000
2001 aa 4 400.0000
2001 aa 16 500.0000
2001 aa 1 500.0000
2001 bb 2 200.0000
2001 bb 8 300.0000
说明：出货表根据进货表的情况，把出货数量拆分出来匹配每次进货的价格，先进先出，本次出货可能对应多次进货，把他们都拆分出来
建表环境： create table 入库表(进货编号 int,品名 varchar(10),单价 money,进货数 int)
insert 入库表 select 1001,'aa',500,5
union all select 1002,'aa',400,10
union all select 1002,'bb',200,2
union all select 1003,'aa',500,20
union all select 1004,'bb',300,20 create table 出库表(出货编号 int,品名 varchar(10),出货数 int)
insert 出库表 select 2001,'aa',11
union all select 2001,'bb',10
union all select 2001,'aa',20
union all select 2001,'aa',1
go
drop table 入库表,出库表
----解决方法
1.需要一个自增加的id列来判断先后
select id=identity(int,1,1),* into 入库表2 from 入库表
select id=identity(int,1,1),* into 出库表2 from 出库表
2
set nocount on
declare @id int,@品名 varchar(200),@出货数量 int,@单价 money,@出货编号 varchar(200),@maxid int, @进货数 int,@pbid int
if not exists (select * from 出库表2) return
set @id=1
select @maxid=max(id) from 出库表2
while @id <=@maxid
begin
select @品名=品名,@出货编号=出货编号,@出货数量=出货数 from (select * from 出库表2 where id=@id)x
if exists(select * from 入库表2 where 品名=@品名 and 进货数>0 )
begin
if exists(select top 1 * from 入库表2 where 品名=@品名 order by id)
begin
select @进货数=进货数,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id) x
if @出货数量 <=@进货数
begin
select @单价=单价 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@出货数量 where id=@pbid and 品名=@品名
end
else if @出货数量>@进货数
begin
select @单价=单价,@进货数=进货数 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@进货数 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
declare @j int,@差额数量 int
set @j=@pbid
select @差额数量=@出货数量-@进货数 from (select top 1* from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
while @差额数量>0
begin
IF NOT exists(select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)
begin
return
end
select @出货数量=进货数 ,@单价=单价,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)x
if @出货数量>=@差额数量
begin
print @出货编号 +','+ @品名+','+cast(@差额数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@差额数量 where id=@pbid and 品名=@品名
select @差额数量=-1
end
else
begin
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
set @差额数量=@差额数量-@出货数量
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
end
set @j=@j+1
end
end
end
end
set @id=@id+1
end
set nocount off /*
2001,aa,5,500.00
2001,aa,6,400.00
2001,bb,2,200.00
2001,bb,8,300.00
2001,aa,4,400.00
2001,aa,16,500.00
2001,aa,1,500.00
*/讲究看下 半年前写的跟你的要求差不多 着是苦力活
1.需要一个自增加的id列来判断先后
select id=identity(int,1,1),* into 入库表2 from 入库表
select id=identity(int,1,1),* into 出库表2 from 出库表
2
set nocount on
declare @id int,@品名 varchar(200),@出货数量 int,@单价 money,@出货编号 varchar(200),@maxid int, @进货数 int,@pbid int
if not exists (select * from 出库表2) return
set @id=1
select @maxid=max(id) from 出库表2
while @id <=@maxid
begin
select @品名=品名,@出货编号=出货编号,@出货数量=出货数 from (select * from 出库表2 where id=@id)x
if exists(select * from 入库表2 where 品名=@品名 and 进货数>0 )
begin
if exists(select top 1 * from 入库表2 where 品名=@品名 order by id)
begin
select @进货数=进货数,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id) x
if @出货数量 <=@进货数
begin
select @单价=单价 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@出货数量 where id=@pbid and 品名=@品名
end
else if @出货数量>@进货数
begin
select @单价=单价,@进货数=进货数 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@进货数 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
declare @j int,@差额数量 int
set @j=@pbid
select @差额数量=@出货数量-@进货数 from (select top 1* from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
while @差额数量>0
begin
IF NOT exists(select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)
begin
return
end
select @出货数量=进货数 ,@单价=单价,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)x
if @出货数量>=@差额数量
begin
print @出货编号 +','+ @品名+','+cast(@差额数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@差额数量 where id=@pbid and 品名=@品名
select @差额数量=-1
end
else
begin
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
set @差额数量=@差额数量-@出货数量
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
end
set @j=@j+1
end
end
end
end
set @id=@id+1
end
set nocount off /*
2001,aa,5,500.00
2001,aa,6,400.00
2001,bb,2,200.00
2001,bb,8,300.00
2001,aa,4,400.00
2001,aa,16,500.00
2001,aa,1,500.00
*/没贴好
这结果有问题把？
进货批次为C的是2008-5-15才进来，你怎么05-02就能把它卖出去呢？
理论上是不能这样的，但是处理还是这样的好
因为入库日期有时候是人为录入晚了，比如说仓库是5.1到货的，系统录入时晚了点(或发票收到晚)
单据编号相当于你的进货号或者出货号:
/*
1、销售定单开票时，不区分货位和批号，只关注数量和价格。
2、销售定单保存时，分摊数量到RESULT表，用STATUS表示数量够或者不够或者需要调拨
3、销售开票单提单时，只提取数量够的，包括已开票但未出库或者未提单的。调拨的需要提前先调拨
4、销售开票单保存时，回写RESUL表的STATUS为已提取，回写JXDJMX中的YIWCHSHL，当YIWCHSHL=SHL时完成。
5、销售出库单提单时，进行分摊批号，必须是库存数量够。
6、销售出库单保存时，回写RESULT表的STATUS为已出库。
*/ALTER proc [dbo].[phft] @djbh varchar(11) as
set nocount on
declare @spid varchar(11),@hshj dec(14,3),@kcshl int,@zxshl int,@zxid int,@zxpihao varchar(30),@zxsxrq varchar(7),@kpshl int
declare @lhkc table (tid int identity(1,1),spid varchar(11),pihao varchar(30),sxrq varchar(7),shl int,hshj dec(14,3))
declare ftph cursor for select spid,hshj,shl from jxdjmx where djbh=@djbh
open ftph
fetch next from ftph into @spid,@hshj,@kpshl
while @@fetch_status=0
begin
insert into @lhkc select @spid,pihao,sxrq,shl,gebjj from sphwph where spid=@spid order by sxrq
select @zxid=isnull(min(tid),0) from @lhkc where spid=@spid
if @zxid<=0
insert into result values(@djbh,@spid,null,null,@kpshl,@hshj,1,null)--status:0够，1不够
else
begin
select @zxshl=shl,@zxpihao=pihao,@zxsxrq=sxrq from @lhkc where spid=@spid and tid=@zxid
while @kpshl>0
begin
if @kpshl<=@zxshl
begin
insert into result values(@djbh,@spid,@zxpihao,@zxsxrq,@kpshl,@hshj,0,null)
set @kpshl=@kpshl-@zxshl
end
else --开票数量大于最小库存批次数量，表示库存可能有几个批次，将进行每个批次减操作，当开票数量《=0时终止
begin
if @kpshl>@zxshl and @zxshl>0
begin
insert into result values(@djbh,@spid,@zxpihao,@zxsxrq,@zxshl,@hshj,0,null)
set @kpshl=@kpshl-@zxshl
set @zxshl=0
set @zxid=@zxid+1
select @zxshl=isnull(shl,0),@zxpihao=pihao,@zxsxrq=sxrq from @lhkc where spid=@spid and tid=@zxid
end
else
begin
insert into result values(@djbh,@spid,null,null,@kpshl,@hshj,0,null)
set @kpshl=0
set @zxshl=0
end
end
end
end
fetch next from ftph into @spid,@hshj,@kpshl
end
close ftph
deallocate ftphselect a.*,b.spbh,b.spmch,b.shpchd,b.shpgg,b.dw,b.pizhwh,b.jlgg,b.zhdcbj,A.HSHJ AS DJ,a.shl*a.hshj as je,a.shl*c.chbdj as chbje,b.pfpj,@djbh as duiydjbh,
'HWI00000001' as hw,'零货库' as huowname,c.chbdj,'001' as hwbh,a.hshj as lshj,a.shl*a.hshj as lshje,a.shl*a.hshj as hsje,b.shengccj,
b.jixing,b.zdshj,b.zgshj,b.zhjj,b.shangplx from result a(nolock) join spkfk b(nolock) on a.spid=b.spid join spkfjc c(nolock) on a.spid=c.spid
where a.status=0 and djbh=@djbh
set nocount on
declare @id int,@品名 varchar(200),@出货数量 int,@单价 money,@出货编号 varchar(200),@maxid int, @进货数 int,@pbid int
if not exists (select * from 出库表2) return
set @id=1
select @maxid=max(id) from 出库表2
while @id <=@maxid
begin
select @品名=品名,@出货编号=出货编号,@出货数量=出货数 from (select * from 出库表2 where id=@id)x
if exists(select * from 入库表2 where 品名=@品名 and 进货数>0 )
begin
if exists(select top 1 * from 入库表2 where 品名=@品名 order by id)
begin
select @进货数=进货数,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id) x
if @出货数量 <=@进货数
begin
select @单价=单价 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@出货数量 where id=@pbid and 品名=@品名
end
else if @出货数量>@进货数
begin
select @单价=单价,@进货数=进货数 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@进货数 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
declare @j int,@差额数量 int
set @j=@pbid
select @差额数量=@出货数量-@进货数 from (select top 1* from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
while @差额数量>0
begin
IF NOT exists(select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)
begin
return
end
select @出货数量=进货数 ,@单价=单价,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)x
if @出货数量>=@差额数量
begin
print @出货编号 +','+ @品名+','+cast(@差额数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@差额数量 where id=@pbid and 品名=@品名
select @差额数量=-1
end
else
begin
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
set @差额数量=@差额数量-@出货数量
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
end
set @j=@j+1
end
end
end
end
set @id=@id+1
end
set nocount off 请问：想将结果输出生成表，应该怎么修改语句
---- -------------------- ----------- -------------------- ------------------------------------------------------
0001 16.5 110 11 2008-05-02 11:01:05.000
0002 10.5 120 11 2008-05-03 11:01:05.000
0001 17.5 110 11 2008-05-05 11:01:05.000
0002 10.5 111 11 2008-05-06 11:01:05.000
0001 17.5 112 11 2008-05-07 11:01:05.000
0002 10.5 127 11 2008-05-08 11:01:05.000
0001 17.5 111 11 2008-05-09 11:01:05.000
0002 10.5 178 11 2008-05-10 11:01:05.000
0001 17.5 150 11 2008-05-11 11:01:05.000
0002 10.5 189 11 2008-05-15 11:01:05.000
0002 10.5 190 11 2008-05-19 11:01:05.000
0002 17.5 123 11 2008-05-20 11:01:05.000
0001 17.5 160 11 2008-05-25 11:01:05.000
0001 17.5 160 11 2008-05-26 11:01:05.000
0002 17.5 128 11 2008-05-28 11:01:05.000
0001 17.5 160 11 2008-05-29 11:01:05.000
0002 17.5 150 11 2008-05-30 11:01:05.000
0001 17.5 160 11 2008-06-01 11:01:05.000
0002 17.5 150 11 2008-06-21 11:01:05.000
0001 17.5 160 11 2008-06-25 11:01:05.000（所影响的行数为 20 行）
declare @a nvarchar
declare @total int --计算某种商品的记录数
declare @goodid nvarchar(50) --商品ID
declare @sum int
declare @get int --商品出库的数量
declare @str nvarchar(500)
set @a = 0
set @goodid = '0001'
select @total = count(*) from tb where id = @goodid
print @total
set @get = 550 --假如编号为0001的商品出库数量为550
while @a < @total
begin
set @str = 'select @b = sum(tb1.number) from (select top '+@a+' * from tb where id='+@goodid+' order by saledate asc) as tb1'
exec sp_executesql @str,N'@b int output',@sum output
set @a = @a + 1
if(@sum > @get)
break
else
continue
end
--print @a
create table #temp
(
id varchar(30) ,
price decimal(18,2) ,
number int ,
ben decimal(18,2) ,
saledate datetime
)
declare @s1 nvarchar
declare @s2 nvarchar
set @s1 = @a - 2
set @s2 = @a - 1
set @str = 'insert into #temp select top '+@s1+' a.id,a.price,a.number,a.ben,a.saledate from tb as a where id='+@goodid+' order by saledate asc '
exec(@str)
set @str = 'insert into #temp select top 1 tb1.id,tb1.price,number=(select '+convert(nvarchar,@get)+'-(select sum(number) from (select top '+@s1+' * from tb where id='+@goodid+' order by saledate asc) as a)),tb1.ben,tb1.saledate from (select top '+@s2+' * from tb where id='+@goodid+' order by saledate asc) as tb1 order by saledate desc '
exec(@str)
select * from #temp order by saledate asc
drop table #temp 结果如下
id price number ben saledate
------------------------------ -------------------- ----------- -------------------- ------------------------------------------------------
0001 16.50 110 11.00 2008-05-02 11:01:05.000
0001 17.50 110 11.00 2008-05-05 11:01:05.000
0001 17.50 112 11.00 2008-05-07 11:01:05.000
0001 17.50 111 11.00 2008-05-09 11:01:05.000
0001 17.50 107 11.00 2008-05-11 11:01:05.000（所影响的行数为 5 行）