create table t (a int);
insert into t
select 1
union all
select 2
union all
select 3
union all
select 4;
目标结果：
a ,avg(Cn,Cn-1)
1 ,NULL
2,(2+1)/2
3,(3+2)/2
4,(4+3)/2
求当前列与前一列的均值，有什么好的办法，可以通过一条语句实现吗？
------解决方案--------------------
create table #t (a INT,id int);
insert into #t
select 1,1
union all
select 2,2
union all
select 3,3
union all
select 4,4
SELECT a,'('+RTRIM(a)+'+'+RTRIM((SELECT a FROM #t WHERE id=t1.id-1))+')/2' [avg]
FROM #t t1
------解决方案--------------------
create table t (a int);
insert into t
select 1
union all
select 2
union all
select 3
union all
select 4;
;with tt
as
(
select *,ROW_NUMBER() over(order by @@servername) ronum
from t
)
select t1.a,(t1.a + t2.a) /2 'avg(Cn,Cn-1)'
from tt t1
left join tt t2
on t1.ronum = t2.ronum + 1
/*
a avg(Cn,Cn-1)
1 NULL
2 1
3 2
4 3
*/
------解决方案--------------------
对了你的除法，是整数的除法吗，在sql server 里面(1)/2 是等于0的，如果是要等于0.5，得这样：
create table t (a int);
insert into t
select 1
union all
select 2
union all
select 3
union all
select 4;
;with tt
as
(
select *,ROW_NUMBER() over(order by @@servername) ronum
from t
)
select t1.a,(t1.a + t2.a) /2.0 'avg(Cn,Cn-1)'
from tt t1
left join tt t2
on t1.ronum = t2.ronum + 1
/*
a avg(Cn,Cn-1)
1 NULL
2 1.500000
3 2.500000
4 3.500000
*/
------解决方案--------------------
create table #tab (a INT,id int);
insert into #tab
select 1,1
union all
select 2,2
union all
select 3,3
union all
select 4,4
select a.a,'('+convert(varchar(20),a.id)+'+'+convert(varchar(20),
case when b.id-1=0 then null else b.id-1 end)+')/2' as [avg(Cn,Cn-1)] from #tab a
left join #tab b on a.id=b.id
a avg(Cn,Cn-1)
----------- ---------------------------------------------
1 NULL
2 (2+1)/2
3 (3+2)/2
4 (4+3)/2
(4 行受影响)
------解决方案--------------------
create table #tab (a INT,id int);
insert into #tab
select 1,1
union all
select 2,2
union all
select 3,3
union all
select 4,4
select a.a,(convert(numeric(18,2),a.id)+
case when b.id-1=0 then null else b.id-1 end )/2 as [avg(Cn,Cn-1)] from #tab a
left join #tab b on a.id=b.id
a avg(Cn,Cn-1)
----------- ---------------------------------------
1 NULL
2 1.500000
3 2.500000
4 3.500000
(4 行受影响)
------解决方案--------------------
create table #t (a int);
insert into #t