sql基于各个时间段历史数据的平均值，跪求！！！
TYPe WeekEndingOnDatethis week
A2012.08.120.998606944444445
B 2012.08.120.997755666666667
D2012.08.120.996660555555555
E2012.08.120.999757916666666
G2012.08.120.998900801282053
C 2012.08.120.999833333333333
F2012.08.120.998623863636364
G2012.08.050.996723519313306
C2012.08.050.998592857142857
B2012.08.050.999676571428571
D2012.08.050.996521428571429
E2012.08.050.999792142857143
F2012.08.050.998750357142857
A2012.08.050.997293809523809
F2012.07.290.998803571428571
A2012.07.290.989981666666667
E2012.07.290.9998175
G2012.07.290.995505000000001
D2012.07.290.997864285714286
B2012.07.290.999384285714286
C2012.07.290.999138571428571
.......
现有类似上图的表结构及数据，这里提供了3周的数据。
现在希望得到各星期对应的上12周的平均数据，请问怎么办，不想用游标之类的。
如 A2012.08.12 对应的上12周的数据的平均值就是 2012.08.12前12个星期 A对应值的平均，求高手解答，请详细点啊。
[解决办法]
declare @T table (TYPe varchar(1),WeekEndingOnDate datetime,thisweek numeric(16,15))insert into @Tselect 'A','2012.08.12',0.998606944444445 union allselect 'B','2012.08.12',0.997755666666667 union allselect 'D','2012.08.12',0.996660555555555 union allselect 'E','2012.08.12',0.999757916666666 union allselect 'G','2012.08.12',0.998900801282053 union allselect 'C','2012.08.12',0.999833333333333 union allselect 'F','2012.08.12',0.998623863636364 union allselect 'G','2012.08.05',0.996723519313306 union allselect 'C','2012.08.05',0.998592857142857 union allselect 'B','2012.08.05',0.999676571428571 union allselect 'D','2012.08.05',0.996521428571429 union allselect 'E','2012.08.05',0.999792142857143 union allselect 'F','2012.08.05',0.998750357142857 union allselect 'A','2012.08.05',0.997293809523809 union allselect 'F','2012.07.29',0.998803571428571 union allselect 'A','2012.07.29',0.989981666666667 union allselect 'E','2012.07.29',0.9998175 union allselect 'G','2012.07.29',0.995505000000001 union allselect 'D','2012.07.29',0.997864285714286 union allselect 'B','2012.07.29',0.999384285714286 union allselect 'C','2012.07.29',0.999138571428571select [TYPe],avg(thisweek) as 平均值from @T where WeekEndingOnDate>dateadd(week,-12,getdate()) --最近12周用where 加个条件即可。group by [TYPe] --求ABCD的分组，把group by 后面改成Type/*TYPe 平均值---- ---------------------------------------A 0.995294140211640B 0.998938841269841C 0.999188253968253D 0.997015423280423E 0.999789186507936F 0.998725930735930G 0.997043106865120*/
[解决办法]
declare @T table (TYPe varchar(1),WeekEndingOnDate datetime,thisweek numeric(16,15))insert into @Tselect 'A','2012.08.12',0.998606944444445 union allselect 'B','2012.08.12',0.997755666666667 union allselect 'D','2012.08.12',0.996660555555555 union allselect 'E','2012.08.12',0.999757916666666 union allselect 'G','2012.08.12',0.998900801282053 union allselect 'C','2012.08.12',0.999833333333333 union allselect 'F','2012.08.12',0.998623863636364 union allselect 'G','2012.08.05',0.996723519313306 union allselect 'C','2012.08.05',0.998592857142857 union allselect 'B','2012.08.05',0.999676571428571 union allselect 'D','2012.08.05',0.996521428571429 union allselect 'E','2012.08.05',0.999792142857143 union allselect 'F','2012.08.05',0.998750357142857 union allselect 'A','2012.08.05',0.997293809523809 union allselect 'F','2012.07.29',0.998803571428571 union allselect 'A','2012.07.29',0.989981666666667 union allselect 'E','2012.07.29',0.9998175 union allselect 'G','2012.07.29',0.995505000000001 union allselect 'D','2012.07.29',0.997864285714286 union allselect 'B','2012.07.29',0.999384285714286 union allselect 'C','2012.07.29',0.999138571428571select *from @Tselect TYPe,WeekEndingOnDate,thisweek,AVG(historyData) as last12Avg from ( select a.*,b.thisweek as historyData from @T a left join @T b --取出每周对应的上12周的数据 on a.TYPe=b.TYPe and b.WeekEndingOnDate >= dateadd(week,-12,a.WeekEndingOnDate) and b.WeekEndingOnDate < a.WeekEndingOnDate ) c group by TYPe,WeekEndingOnDate,thisweek --取出数据后分组求上12周数据的均值 order by WeekEndingOnDate,TYPeTYPe WeekEndingOnDate thisweek last12AvgA 2012-07-29 00:00:00.000 0.989981666666667 NULLB 2012-07-29 00:00:00.000 0.999384285714286 NULLC 2012-07-29 00:00:00.000 0.999138571428571 NULLD 2012-07-29 00:00:00.000 0.997864285714286 NULLE 2012-07-29 00:00:00.000 0.999817500000000 NULLF 2012-07-29 00:00:00.000 0.998803571428571 NULLG 2012-07-29 00:00:00.000 0.995505000000001 NULLA 2012-08-05 00:00:00.000 0.997293809523809 0.989981666666667B 2012-08-05 00:00:00.000 0.999676571428571 0.999384285714286C 2012-08-05 00:00:00.000 0.998592857142857 0.999138571428571D 2012-08-05 00:00:00.000 0.996521428571429 0.997864285714286E 2012-08-05 00:00:00.000 0.999792142857143 0.999817500000000F 2012-08-05 00:00:00.000 0.998750357142857 0.998803571428571G 2012-08-05 00:00:00.000 0.996723519313306 0.995505000000001A 2012-08-12 00:00:00.000 0.998606944444445 0.993637738095238B 2012-08-12 00:00:00.000 0.997755666666667 0.999530428571428C 2012-08-12 00:00:00.000 0.999833333333333 0.998865714285714D 2012-08-12 00:00:00.000 0.996660555555555 0.997192857142857E 2012-08-12 00:00:00.000 0.999757916666666 0.999804821428571F 2012-08-12 00:00:00.000 0.998623863636364 0.998776964285714G 2012-08-12 00:00:00.000 0.998900801282053 0.996114259656653
