求去年某月到今年某月的平均值

[xs1259411652]

年 月 销量
2010 1 500
2010 2 252
2010 3 700
2010 4 800
2010 5 450
2010 6 500
2010 7 360
2010 8 500
2010 9 780
2010 10 840
2010 11 880
2010 12 1000
2011 1 1400
2011 2 574
2011 3 700
2011 4 447
2011 5 1147
2011 6 147
2011 7 360
2011 8 1474
2011 9 780
2011 10 1474
2011 11 880
2011 12 541
2012 1 500
2012 2 780

通过select语句得到一张新表，如何实现
每个月的销量为前12个月的平均值

如：2012年2月的销量为2011年2月到2012年1月销量的平均值
2012年1月的销量为2010年1月到2011年12月销量的平均值
2011年12月的销量为2010年12月到2011年1月销量的平均值
以此类推

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([year] int,[month] int,[value] int)
insert [test]
select 2010,1,500 union all
select 2010,2,252 union all
select 2010,3,700 union all
select 2010,4,800 union all
select 2010,5,450 union all
select 2010,6,500 union all
select 2010,7,360 union all
select 2010,8,500 union all
select 2010,9,780 union all
select 2010,10,840 union all
select 2010,11,880 union all
select 2010,12,1000 union all
select 2011,1,1400 union all
select 2011,2,574 union all
select 2011,3,700 union all
select 2011,4,447 union all
select 2011,5,1147 union all
select 2011,6,147 union all
select 2011,7,360 union all
select 2011,8,1474 union all
select 2011,9,780 union all
select 2011,10,1474 union all
select 2011,11,880 union all
select 2011,12,541 union all
select 2012,1,500 union all
select 2012,2,780


with t
as(
select px=ROW_NUMBER()over(order by getdate()),
* from test
)
select *,isnull((select avg(value) from(
select top 12 value from t b where a.px>b.px order by b.px)s),0) as 销量
from t a
/*
px	year	month	value	销量
---------------------------------------------
1	2010	1	500	0
2	2010	2	252	500
3	2010	3	700	376
4	2010	4	800	484
5	2010	5	450	563
6	2010	6	500	540
7	2010	7	360	533
8	2010	8	500	508
9	2010	9	780	507
10	2010	10	840	538
11	2010	11	880	568
12	2010	12	1000	596
13	2011	1	1400	630
14	2011	2	574	630
15	2011	3	700	630
16	2011	4	447	630
17	2011	5	1147	630
18	2011	6	147	630
19	2011	7	360	630
20	2011	8	1474	630
21	2011	9	780	630
22	2011	10	1474	630
23	2011	11	880	630
24	2011	12	541	630
25	2012	1	500	630
26	2012	2	780	630
*/

[开着拖拉机泡妞]

-->TravyLee生成测试数据[test]
if object_id('[test]') is not null drop table [test]
create table [test]([year] int,[month] int,[value] int)
insert [test]
select 2010,1,500 union all
select 2010,2,252 union all
select 2010,3,700 union all
select 2010,4,800 union all
select 2010,5,450 union all
select 2010,6,500 union all
select 2010,7,360 union all
select 2010,8,500 union all
select 2010,9,780 union all
select 2010,10,840 union all
select 2010,11,880 union all
select 2010,12,1000 union all
select 2011,1,1400 union all
select 2011,2,574 union all
select 2011,3,700 union all
select 2011,4,447 union all
select 2011,5,1147 union all
select 2011,6,147 union all
select 2011,7,360 union all
select 2011,8,1474 union all
select 2011,9,780 union all
select 2011,10,1474 union all
select 2011,11,880 union all
select 2011,12,541 union all
select 2012,1,500 union all
select 2012,2,780

--更正一下，改一下排序规则
with t
as(
select px=ROW_NUMBER()over(order by getdate()),
* from test
)
select *,isnull((select avg(value) from(
select top 12 value from t b where a.px>b.px order by b.px desc)s),0) as 销量
from t a
/*
px	year	month	value	销量
------------------------------------------
1	2010	1	500	0
2	2010	2	252	500
3	2010	3	700	376
4	2010	4	800	484
5	2010	5	450	563
6	2010	6	500	540
7	2010	7	360	533
8	2010	8	500	508
9	2010	9	780	507
10	2010	10	840	538
11	2010	11	880	568
12	2010	12	1000	596
13	2011	1	1400	630
14	2011	2	574	705
15	2011	3	700	732
16	2011	4	447	732
17	2011	5	1147	702
18	2011	6	147	760
19	2011	7	360	731
20	2011	8	1474	731
21	2011	9	780	812
22	2011	10	1474	812
23	2011	11	880	865
24	2011	12	541	865
25	2012	1	500	827
26	2012	2	780	752
*/