sql基于各个时间段历史数据的平均值，跪求！解决方案

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*/
------解决方案--------------------
SQL code
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