请教怎么算出这个数

Question

select sum(kcl)/datediff(day,d1,d2) from (
select select (sum(jcsl)-sum(ccsl)) as kcl 
 from table
  where ymd between d1 and d2 group by convert(varchar(10),ymd,120)
) a

Answer 1

设个循环，动态生成SQL语句，然后再执行不就可以了吗

Answer 2

select sum(kcl)/datediff(day,d1,d2) from (
select (sum(jcsl)-sum(ccsl)) as kcl 
 from table
  where ymd between d1 and d2 group by convert(varchar(10),ymd,120)
) a

Answer 3

select sum(jcsl-ccsl)*1.0/count(*) as AVGStorage from t1 where ymd between '2002-3-23' and '2002-4-20'
or
select avg(jcsl-ccsl) as AVGStorage from t1 where ymd between '2002-3-23' and '2002-4-20'

Answer 4

如果那一天没记录，一天多记录 OpenVMS，你的就不行

Answer 5

上午回复不见了!
题意理解是不是有问题:declare @Temp table (kcl numeric(10,2))
declare @dt datetime
declare @kcl numeric(10,2)
select @kcl=sum(jcsl)-sum(ccsl) 
 from table
  where ymd <= @dt1
insert @temp values(@kcl)
select @dt=dateadd(day,1,@dt1)while @dt<=@dt2
begin
select @kcl=@kcl+sum(jcsl)-sum(ccsl) 
 from table
  where ymd > dateadd(day,-1,@dt)
  where ymd <= @dt
insert @temp values(@kcl)
select @dt=dateadd(day,1,@dt1)
endselect avg(kcl) as avgkcl from @Temp

Answer 6

更正笔误:declare @Temp table (kcl numeric(10,2))
declare @dt datetime
declare @kcl numeric(10,2)
select @kcl=sum(jcsl)-sum(ccsl) 
 from table
  where ymd <= @dt1
insert @temp values(@kcl)
select @dt=dateadd(day,1,@dt1)while @dt<=@dt2
begin
select @kcl=@kcl+sum(jcsl)-sum(ccsl) 
 from table
  where ymd > dateadd(day,-1,@dt)
  where ymd <= @dt
insert @temp values(@kcl)
select @dt=dateadd(day,1,@dt)
endselect avg(kcl) as avgkcl from @Temp

Answer 7

--如果你能保证一天在TABLE1表中只对应一笔记录,而且有且只有一笔记录
select avg(ccs)
from (select (select sum(b.jcsl) - sum(b.ccsl) from table6 b where b.ymd = a.ymd) as ccs
   from table6 a
  where ymd between '2002-1-1' and '2002-1-30') as t --如果你不能保证一天在TABLE1表中只对应一笔记录，如果有段时间库存为0也可以计算出平均数
select sum(ccs) * 1.0 / (DATEDIFF(d,'2002-1-1','2002-1-30') + 1)
from (select (select sum(b.jcsl) - sum(b.ccsl) from table6 b where b.ymd = a.ymd) as ccs
   from table6 a
  where ymd between '2002-1-1' and '2002-1-30') as t

Answer 8

Yang_的好速度快!这种语句写成一句一点都不好。我们用BETWEEN结果全错

Answer 9

CSDN比蜗牛还慢，半天都打不开：（
谢谢各位的回答，由于我的YMD是字符字段，所以我要改改，等试成功了再结帖，希望还能冲上来，CSDN太慢了：（

Answer 10

实在很对不起，我对SQL的函数不熟悉，我的YMD字段是10位的字符型."2002/01/01"这样的格式，请问如果修改 Yang_(扬帆破浪)的答案以便测试呢？如果我要求这个存储过程是返回这个平均值又该怎样，谢谢！！
如果可以的话把答案发给我的信箱[email protected]。因为CSDN太难上了

Answer 11

declare @Temp table (kcl numeric(10,2))
declare @dt datetime
declare @kcl numeric(10,2)
select @kcl=sum(jcsl)-sum(ccsl) 
 from table
  where convert(datetime,ymd) <= @dt1
insert @temp values(@kcl)
select @dt=dateadd(day,1,@dt1)while @dt<=@dt2
begin
select @kcl=@kcl+sum(jcsl)-sum(ccsl) 
 from table
  where convert(datetime,ymd) > dateadd(day,-1,@dt)
  where convert(datetime,ymd) <= @dt
insert @temp values(@kcl)
select @dt=dateadd(day,1,@dt)
endselect avg(kcl) as avgkcl from @Temp

Answer 12

有问题啊，下面那里怎么有两个WHERE子句的？？
另外我要修改@temp的结构，增加一个name字段和一个自增长字段，要怎么定义呢。因为我要改成：
insert into @temp
(select distinct name,@kcl=sum(jcsl)-sum(ccsl)
 from cl_year
  where convert(datetime,ymd) <= @dt1
   group by name)
好象不行？？

Answer 13

更正我刚才写的，应该是小于等于（b.ymd <= a.ymd），错写成了等于--如果你能保证一天在TABLE1表中只对应一笔记录,而且有且只有一笔记录
select avg(ccs)
from (select (select sum(b.jcsl) - sum(b.ccsl) from table6 b where b.ymd <= a.ymd) as ccs
   from table6 a
  where ymd between '2002-1-1' and '2002-1-30') as t --如果你不能保证一天在TABLE1表中只对应一笔记录，如果有段时间库存为0也可以计算出平均数
select sum(ccs) * 1.0 / (DATEDIFF(d,'2002-1-1','2002-1-30') + 1)
from (select (select sum(b.jcsl) - sum(b.ccsl) from table6 b where b.ymd <= a.ymd) as ccs
   from table6 a
  where ymd between '2002-1-1' and '2002-1-30') as t

Answer 14

其实写成一句也有写成一句的道理，写成一句至少可以在前台生成SQL语句没必要到后台建立存储过程。另外写成存储过程之所以快，快就快在了@kcl=@kcl+sum(jcsl)-sum(ccsl) 这句上，这样就没有必要反复的统计每日库存，而是直接用昨日库存量加今日库存变化两就可以计算出今日库存，这样的确是快了很多，尤其是针对连续数据特别明显，但如果库存时间长度比较大数量不多且数据离散性很强的情况下（比如库存分很多种商品，每天都有出入库操作，但对某一特定商品，其出入库操作可能非常有限而且时间间隔很大），用一句的查询要快于使用存储过程，因为存储过程要运算从开始日期到截止日期每一天的数据可这些数据并不一定在数据库中存在

Answer 15

如果某天没有记录,结果有问题.(Yang_没有测试外)
set nocount on
declare @ymd1 datetime,@ymd2 datetime,@i int,@kcl int
set @ymd1='2002-1-26'
set @ymd2='2002-3-25'
set @i=0
select top 0 jcsl kcl,ymd into #t1 from t1
while dateadd(d,@i,@ymd1)<=@ymd2
begin
  insert into #t1
  select isnull(sum(jcsl-ccsl),0) kcl,dateadd(d,@i,@ymd1) ymd  from t1
  where ymd=dateadd(d,@i,@ymd1)
  set @i=@i+1
endupdate a
set kcl=(select sum(kcl) from #t1 where ymd<=a.ymd)
from #t1 aselect * from t1
select * from #t1select sum(kcl)*1.0/count(*) as DayAvgStorage from #t1
drop table #t1---
results:DayAvgStorage              
-------------------------- 
894.915254237288t1:jcsl        ccsl        ymd    
----------- ----------- 
300         100         2002-01-26 00:00:00.000
900         100         2002-01-26 00:00:00.000
300         700         2002-02-01 00:00:00.000
800         200         2002-03-01 00:00:00.000
200         100         2002-04-01 00:00:00.000
250         150         2002-05-01 00:00:00.000
600         800         2002-06-01 00:00:00.000
400         200         2002-07-01 00:00:00.000

Answer 16

如果是平均库存,和一天有没有出入库是没有关系的,没有出入库的那天的库存和上一天相同.
以下是加了Name后的代码:declare @Temp table (Name varchar(30),kcl numeric(10,2),Days int)
declare @dt datetime
declare @Days intinsert @Temp
select Name,sum(jcsl)-sum(ccsl),0 
 from tablename
  where convert(datetime,ymd) <= @dt1
  group by Nameselect @dt=dateadd(day,1,@dt1),@Days=0while @dt<=@dt2
begininsert @Temp
select Name,sum(jcsl)-sum(ccsl),@Days
 from tablename
  where convert(datetime,ymd) > dateadd(day,-1,@dt)
  where convert(datetime,ymd) <= @dt
  group by Nameselect @dt=dateadd(day,1,@dt),@Days=@Days+1
endupdate a
  set kcl=(select sum(kcl) from @Temp where name=a.name and days<=a.Days)
  from @Temp aselect avg(kcl) as avgkcl from @Temp

Answer 17

TO: YANG_ 我的意思是某天没有记录,上面的好多回答有错误,你的没有测试
这次的结果正确,除了一点小问题where convert(datetime,ymd) > dateadd(day,-1,@dt)
  where convert(datetime,ymd) <= @dt --> and convert...
  存储过程的写法:
create proc getDayAvgStorage
@ymd1 datetime,
@ymd2 datetime,
@j decimal(8,2) OUTPUT
AS
BEGIN
set nocount on
declare @i int,@kcl intset @i=0
select top 0 jcsl kcl,ymd into #t1 from t1
while dateadd(d,@i,@ymd1)<=@ymd2
begin
  insert into #t1
  select isnull(sum(jcsl-ccsl),0) kcl,dateadd(d,@i,@ymd1) ymd  from t1
  where ymd=dateadd(d,@i,@ymd1)
  set @i=@i+1
endupdate a
set kcl=(select sum(kcl) from #t1 where ymd<=a.ymd)
from #t1 aset @j=(select sum(kcl)*1.0/count(*) as DayAvgStorage from #t1)
drop table #t1ENDdeclare @j decimal(8,2)
exec getDayAvgStorage '2002-1-26','2002-3-25',@j OUTPUT
select @j

Answer 18

好象只有Yang_(扬帆破浪) 大哥的可以通过，但还要验证一下数据的正确性，计算30天的数据要6秒(不过我的电脑配置很低，放到服务器应该1秒就可以了。
呵呵，开始怎么都通过不了，仔细看看，原来他使用的是SQLSERVER2000，可以使用表变量，我的SQLSERVER7当然不行了：）
等等再结帖，谢谢了。

Answer 19

to: raymen(小陈) 
表变量可以改成临时表!
呵呵,我现在喜欢用表变量了!

Answer 20

其实这个问题可以转换一下算法。如下：
设某天的库存余额为bal(date)，第二天的库存余额为bal(date+1)。
某天的入库数为in(date)，出库数为out(date)。
则我们可以知道：
bal(date+1)=bal(date)+in(date+1)-out(date+1)
bal(date+2)=bal(date+1)+in(date+2)-out(date+2)
           =bal(date)+in(date+1)-out(date+1)+in(date+2)-out(date+2)所以:
bal(date)+bal(date+1)+bal(date+2)
=3*bal(date)
+2*( in(date+1)-out(date+1) )
+1*( in(date+2)-out(date+2) )看出规律了吗？
依此类推，从DATE开始N天的平均库存额为：
(   n*bal(date)
    +(n-1)*( in(date+1)-out(date+1) ) 
    +(n-2)*( in(date+2)-out(date+2) )
    +.....
    +1*( in(date+N-1)-out(date+N-1) )     )/n其中bal(date)是所有小于等于date的sum(in-out)。所以求平均库存额的方法可以简化成：declare @begindate datetime
declare @enddate datetime
set @begindate='2001-1-1'
set @enddate='2002-1-24'
declare @days int
select @days=datediff(day,@begindate,@enddate) + 1 select name,
sum( (case when ymd<@begindate 
      then @days
      else (@days- datediff(day,@begindate,ymd)) 
      end)
     *(jcsj-ccsj)
     /@days
) as avgbal
from mytable 
where ymd <= @enddate
group by name这样应该效率高一些。因为这样就不需要在对每个日期进行循环时都做一次分组求和的操作，那样做对表进行的扫描次数太多，导致有些过程其实是重复的，比如算第二天时，其实是把第一天算的又重算了一次，只是加上了第二天的出入库值，所以我觉得那样效率会不高。我这里日期字段用的datetime型，因为这样方便些，要不函数太多，会晕掉的：）
你转一下就可以了。

Answer 21

select average(a.sl) from (
select sum(jcsl)-sum(ccsl) 
 from table
  where ymd <= 日期2 and ymd>=日期1
group by ymd
)a

Answer 22

大家测试过吗?按照按钮的办法,算1-26到2-1结果是:
avgbal      
----------- 
123
表数据为:
jcsl        ccsl        ymd    
----------- ----------- 
300         100         2002-01-26 00:00:00.000
900         100         2002-01-26 00:00:00.000
300         700         2002-02-01 00:00:00.000
800         200         2002-03-01 00:00:00.000
200         100         2002-04-01 00:00:00.000实际结果应该为:
---------- 
894.92
(1 row(s) affected)
什么原因呢? 是不是因为日期不连续?那样的话,如果日期连续,这问题不会那么复杂了.

Answer 23

OpenVMS(半知半解) :奇怪，我这里运行结果是对的啊，你再试试？ 搞不好又是MSSQL有BUG呢。我的算法不需要考虑日期不连续的问题，因为(jcsj-ccsj)前面乘的系数已经是将这些没有记录日期算进去了。

Answer 24

TO:： icevi(按钮工厂) 
我用上面的数据试了,好象不对,符合条件的4条记录,你只算了4次
declare @begindate datetime
declare @enddate datetime
set @begindate='2001-1-26'
set @enddate='2002-3-25'
declare @days int
select @days=datediff(day,@begindate,@enddate) + 1 select ---name,
 (case when ymd<@begindate 
      then @days
      else (@days- datediff(day,@begindate,ymd)) 
      end)
     *(jcsl-ccsl)
     /@days
 as avgbal
from t1
where ymd <= @enddate-----
avgbal      
----------- 
27
111
-50
35(4 row(s) affected)
看看怎么回事,请把你的测试数据和结果,帖出来

Answer 25

OpenVMS(半知半解) ：
我的测试代码：
create table mytable (name varchar(10),fdate datetime,fin numeric(10,2),fout numeric(10,2))
insert into mytable (fin,fout,fdate,name) values (300  ,       100  ,       '2002-01-26 00:00:00.000','001')
insert into mytable (fin,fout,fdate,name) values (900 ,        100 ,        '2002-01-26 00:00:00.000','001')
insert into mytable (fin,fout,fdate,name) values (300 ,        700 ,        '2002-02-01 00:00:00.000','001')
insert into mytable (fin,fout,fdate,name) values (800 ,        200 ,        '2002-03-01 00:00:00.000','001')
insert into mytable (fin,fout,fdate,name) values (200 ,        100,         '2002-04-01 00:00:00.000','001')declare @begindate datetime
declare @enddate datetime
set @begindate='2002-1-26'
set @enddate='2002-3-25'
declare @days int
select @days=datediff(day,@begindate,@enddate) + 1 
print @days--看中间处理得到的数据
select  (case when fdate<@begindate 
      then @days
      else (@days- datediff(day,@begindate,fdate)) 
      end) as f1,
(fin-fout) as f2,
@days as f3,
(case when fdate<@begindate 
then @days 
else (@days-datediff(day,@begindate,fdate)) 
end)
*(fin-fout)
/@days as f4
from mytable 
where fdate <= @enddate--求平均库存
select name,
sum( (case when fdate<@begindate 
      then @days
      else (@days- datediff(day,@begindate,fdate)) 
      end)
     *(fin-fout)
     /@days
) as avgbal
from mytable 
where fdate <= @enddate
group by name结果：
59
f1          f2            f3          f4                                  
----------- ------------- ----------- ----------------------------------- 
59          200.00        59          200.0000000000000
59          800.00        59          800.0000000000000
53          -400.00       59          -359.3220338983050
25          600.00        59          254.2372881355932（所影响的行数为 4 行）name       avgbal                                   
---------- ---------------------------------------- 
001        894.9152542372882（所影响的行数为 1 行）我的表字段名不一样。对照一下就好了。返回的第一个记录集中的f1是每一笔记录应该乘的系数,F2是每天的净入库数，F3是总天数，F4=f1*f2/F3,最后的avgbal是sum(f4)。比如第一天2002-01-26的一笔记录是(入库-出库)=200，前面乘以系数59，则最后求平均库存时，是以200*59参与去求平均库存的，所以若2002-01-27没有记录，乘这个系数的作用这使这样的日期也继承了前面日期的余额。所以第一个记录集虽然只是有四条记录，但乘以系数后，没有发生额的日期的库存余额也是算进去了的。奇怪的是你的运算结果与我的不同，原因在哪里呢？

Answer 26

TO:icevi(按钮工厂) 
mm,very sorry!
set @begindate='2001-1-26' 我的粗心,把2002写成2001,所以结果不对
:-(最近的CSDN见鬼乐,2天就没有上得了~~~~~~~~~~

Answer 27

我用按钮姐姐的方法和扬风破浪的方法结果是一样的，只是按钮姐姐的方法比另外一个快几倍。
谢谢各位的帮忙，CSDN好不容易上来了，终于可以结帐了：）