34,590
社区成员
发帖
与我相关
我的任务
分享
create table os(产品类型 varchar(10),入库 int,出库 int,日期 datetime)
insert into os select 'A100',50,30,'2008-02-02'
insert into os select 'A200',30,10,'2008-02-02'
insert into os select 'A100',10,20,'2008-02-03'
insert into os select 'A200',0,10,'2008-02-03'
insert into os select 'A100',50,20,'2008-02-06'
insert into os select 'A100',20,30,'2008-02-20'
select 产品类型,sum(sl*ts)*1.00/datediff(day,'2008-02-01',dateadd(day,1,'2008-02-20')) as 日均库存 from (
select 产品类型,日期,
isnull((select sum(入库-出库) from os where 产品类型 = a.产品类型 and 日期 <= a.日期),0) as sl,
datediff(day,日期,isnull((select min(日期) from os where 产品类型=a.产品类型 and 日期>a.日期),dateadd(day,1,'2008-02-20'))) as ts
from os a where 日期 between '2008-02-01' and '2008-02-20' group by 产品类型,日期) b
group by 产品类型
/*
产品类型 日均库存
---------- ---------------------------
A100 32.0000000000000
A200 10.0000000000000
*/
drop table os
--执行存储过程:
exec www_p '2008-02-01','2008-02-20'
create proc www_p
@start datetime,
@end datetime
as
declare @start1 datetime
set @start1=@start
declare @t table(产品类型 varchar(50),data datetime)
declare @产品类型 varchar(50)
declare cur cursor for select distinct 产品类型 from os
open cur
fetch next from cur into @产品类型
while(@@fetch_status=0)
begin
while(@start<=@end)
begin
insert into @t select @产品类型,@start
set @start=dateadd(dd,1,@start)
end
set @start=@start1
fetch next from cur into @产品类型
end
close cur
deallocate cur
select b.产品类型,b.data,库存 into # from @t b left join
(select 产品类型,日期,
库存=(select isnull(sum(入库)-sum(出库),0) from os where a.产品类型=产品类型 and 日期<a.日期)+(a.入库-a.出库)
from os a)c
on b.data=c.日期 and b.产品类型=c.产品类型
select 产品类型,日均库存量=sum(库存)*1.0/count(1) from
(select 产品类型,data,
库存=isnull(case isnull(库存,'') when '' then
(select 库存 from # b where b.产品类型=a.产品类型 and b.data<a.data and isnull(b.库存,'')!='' and not exists
(select 1 from # where 产品类型=b.产品类型 and data>b.data and data<a.data and isnull(库存,'')!=''))
else 库存 end,0)
from # a)s
group by 产品类型
--这题比较麻烦。做出来了效率也很低。。
create table os(产品类型 varchar(10),入库 int,出库 int,日期 datetime)
insert into os select 'A100',50,30,'2008-02-02'
insert into os select 'A200',30,10,'2008-02-02'
insert into os select 'A100',10,20,'2008-02-03'
insert into os select 'A200',0,10,'2008-02-03'
insert into os select 'A100',50,20,'2008-02-06'
insert into os select 'A100',20,30,'2008-02-20'
create proc www_p
@start datetime,
@end datetime
as
declare @start1 datetime
set @start1=@start
declare @t table(产品类型 varchar(50),data datetime)
declare @产品类型 varchar(50)
declare cur cursor for select distinct 产品类型 from os
open cur
fetch next from cur into @产品类型
while(@@fetch_status=0)
begin
while(@start<=@end)
begin
insert into @t select @产品类型,@start
set @start=dateadd(dd,1,@start)
end
set @start=@start1
fetch next from cur into @产品类型
end
close cur
deallocate cur
select b.产品类型,b.data,库存 into # from @t b left join
(select 产品类型,日期,
库存=(select isnull(sum(入库)-sum(出库),0) from os where a.产品类型=产品类型 and 日期<a.日期)+(a.入库-a.出库)
from os a)c
on b.data=c.日期 and b.产品类型=c.产品类型
select 产品类型,日均库存量=sum(库存)*1.0/count(1) from
(select 产品类型,data,
库存=isnull(case isnull(库存,'') when '' then
(select 库存 from # b where b.产品类型=a.产品类型 and b.data<a.data and isnull(b.库存,'')!='' and not exists
(select 1 from # where 产品类型=b.产品类型 and data>b.data and data<a.data and isnull(库存,'')!=''))
else 库存 end,0)
from # a)s
group by 产品类型
exec www_p '2008-02-01','2008-02-20'
按照楼主的算法;A100应该为:
select (0+20+10+10+10+40+40+40+40+40+40+40+40+40+40+40+40+40+40+30)*1.0/20
可结果是32.000000噢。楼主再说明下
--设表名为tb
select 产品型号,sum(数量)/count(*) from
(select 产品型号,sum(入库-出库) 数量,日期 from tb group by 产品型号,日期) a
group by 产品型号