新建一个表
create table Test(year int,num int)
insert into Test values(2011,20)
insert into Test values(2010,10)
insert into Test values(2009,40)
insert into Test values(2011,30)
insert into Test values(2009,20)
现在要求用一条语句实现下面效果
年份 当年均值 上年均值 下一年均值
2010 10 30 25
create table Test(year int,num int)
insert into Test values(2011,20)
insert into Test values(2010,10)
insert into Test values(2009,40)
insert into Test values(2011,30)
insert into Test values(2009,20)
现在要求用一条语句实现下面效果
年份 当年均值 上年均值 下一年均值
2010 10 30 25
union
select avg(num) as '上年均值' from Test where year=year(getdate())-1
union
select avg(num) as '下一年均值' from Test where year=year(getdate())+1
with cte
(select avg(num) as avgnum
from t
group by year
)
select ThisYear.year, ThisYear.avgnum, LastYear.avgnum, NextYear.avgnum
from cte ThisYear outer join cte LastYear on LastYear+1 = ThisYear.year
outer join cte NextYear on NextYear.year -1 = ThisYear.year
order by ThisYear.year desc
with cte
as
...