27,579
社区成员
发帖
与我相关
我的任务
分享
create table test (YYMM varchar(10),QTY int)
insert test
select '201310',100 union all
select '201311',300 union all
select '201312',220 union all
select '201401',100 union all
select '201402',500 union all
select '201403',700 union all
select '201404',800 union all
select '201405',100 union all
select '201406',500 union all
select '201407',200
--子查询做运算
SELECT yymm,
qty=(SELECT SUM(a.qty)/3 FROM test a WHERE a.yymm<=test.yymm AND a.yymm>=left(CONVERT(varchar(100),dateadd(MM,-2,CONVERT(datetime,test.YYMM+'01')),112),6))
FROM dbo.test WHERE yymm>='201401'
create table test (YYMM varchar(10),QTY int)
insert test
select '201310',100 union all
select '201311',300 union all
select '201312',220 union all
select '201401',100 union all
select '201402',500 union all
select '201403',700 union all
select '201404',800 union all
select '201405',100 union all
select '201406',500 union all
select '201407',200
select a.YYMM,(a.QTY+b.QTY+c.QTY)/3
from test a
left join test b
on a.YYMM=left(CONVERT(varchar(100),dateadd(MM,1,CONVERT(datetime,b.YYMM+'01')),112),6)
left join test c
on a.YYMM=left(CONVERT(varchar(100),dateadd(MM,2,CONVERT(datetime,c.YYMM+'01')),112),6)
where a.YYMM>left(CONVERT(varchar(100),'2014-01-01',112),6)
/*
201401 206
201402 273
201403 433
201404 666
201405 533
201406 466
201407 266
*/