这个是不是你想要的?create table #tmp ( recordDate varchar(50) ,cl1_40 int ,cl2_60 int ,cl3_70 int ) insert into #tmp(recordDate,cl1_40,cl2_60,cl3_70) select '2013-01-01',0, 14, 0 union all select '2013-01-02', 0, 115, 0 union all select '2013-01-03', 0, 128, 0 union all select '2013-01-04', 0, 103, 0 union all select '2013-01-05', 0, 64 ,0 union all select '2013-01-06', 0, 12 ,0 union all select '2013-01-07', 0, 43 ,0 union all select '2013-01-08', 0, 89 ,0 union all select '2013-01-09', 0, 59 ,0 union all select '2013-01-10', 0, 74 ,0select * from #tmpselect * ,( select SUM(t1.cl2_60)*1.0/Max(DATEPART(day,t1.recordDate)) from #tmp t1 where t1.recordDate<=t.recordDate ) as 'DataOfYouWant' from #tmp t drop table #tmp
在看看这个 create table #tmp ( recordDate varchar(50) ,cl1_40 int ,cl2_60 int ,cl3_70 int ) insert into #tmp(recordDate,cl1_40,cl2_60,cl3_70) select '2013-01-01',0, 14, 0 union all select '2013-01-02', 0, 115, 0 union all select '2013-01-03', 0, 128, 0 union all select '2013-01-04', 0, 103, 0 union all select '2013-01-05', 0, 64 ,0 union all select '2013-02-06', 0, 12 ,0 union all select '2013-02-07', 0, 43 ,0 union all select '2013-02-08', 0, 89 ,0 union all select '2013-02-09', 0, 59 ,0 union all select '2013-02-10', 0, 74 ,0select * from #tmpselect * ,( select SUM(t1.cl2_60)*1.0/Max(DATEPART(day,t1.recordDate)) from #tmp t1 where t1.recordDate<=t.recordDate and DATEPART(year,t1.recordDate)= DATEPART(year,t.recordDate) and DATEPART(MONTH,t1.recordDate)= DATEPART(MONTH,t.recordDate) ) as 'DataOfYouWant' from #tmp t
select result= AVG(每天的数量)
from table
where 日期时间 between cast(convert(varchar(10),DATEADD("day", -4, getdate()),121) as datetime) and GETDATE()
SODATE 40 60 70
2013-01-01 0 14 0
2013-01-02 0 115 0
2013-01-03 0 128 0
2013-01-04 0 103 0
2013-01-05 0 64 0
2013-01-06 0 12 0
2013-01-07 0 43 0
2013-01-08 0 89 0
2013-01-09 0 59 0
2013-01-10 0 74 0就比如说这个数据
然后我要算出每天的平均数.每天都要算...
那么1号的[60]就应该等于 14/1=14
那么2号的[60]就应该等于 (14+115)/2=64.5不知道我这样说更清楚没
(
recordDate varchar(50)
,cl1_40 int
,cl2_60 int
,cl3_70 int
)
insert into #tmp(recordDate,cl1_40,cl2_60,cl3_70)
select '2013-01-01',0, 14, 0
union all select '2013-01-02', 0, 115, 0
union all select '2013-01-03', 0, 128, 0
union all select '2013-01-04', 0, 103, 0
union all select '2013-01-05', 0, 64 ,0
union all select '2013-01-06', 0, 12 ,0
union all select '2013-01-07', 0, 43 ,0
union all select '2013-01-08', 0, 89 ,0
union all select '2013-01-09', 0, 59 ,0
union all select '2013-01-10', 0, 74 ,0select * from #tmpselect *
,(
select SUM(t1.cl2_60)*1.0/Max(DATEPART(day,t1.recordDate))
from #tmp t1
where t1.recordDate<=t.recordDate
) as 'DataOfYouWant'
from #tmp t
drop table #tmp
多谢指教 我验算了一下数据 在1月的时候 整个数据都是正确的.
但是一旦跨月了就有错了.
比如我的数据
SODATE 60
2013-02-01 66
2013-02-02 67
2013-02-03 12
用你这个算法算出来就不对了 还请指教下
create table #tmp
(
recordDate varchar(50)
,cl1_40 int
,cl2_60 int
,cl3_70 int
)
insert into #tmp(recordDate,cl1_40,cl2_60,cl3_70)
select '2013-01-01',0, 14, 0
union all select '2013-01-02', 0, 115, 0
union all select '2013-01-03', 0, 128, 0
union all select '2013-01-04', 0, 103, 0
union all select '2013-01-05', 0, 64 ,0
union all select '2013-02-06', 0, 12 ,0
union all select '2013-02-07', 0, 43 ,0
union all select '2013-02-08', 0, 89 ,0
union all select '2013-02-09', 0, 59 ,0
union all select '2013-02-10', 0, 74 ,0select * from #tmpselect *
,(
select SUM(t1.cl2_60)*1.0/Max(DATEPART(day,t1.recordDate))
from #tmp t1
where t1.recordDate<=t.recordDate
and DATEPART(year,t1.recordDate)= DATEPART(year,t.recordDate)
and DATEPART(MONTH,t1.recordDate)= DATEPART(MONTH,t.recordDate)
) as 'DataOfYouWant'
from #tmp t