SQL统计(100分)

有一表，字段和记录如下：
A1 A2
1 2
2 3
4 1
5 6
其中A1字段的值不连续，不重复，现在要求统计和当前记录中字段A1的相等或值小一点的2条记录中字段A2的值的和，如统计A1=2的记录时 Sum（a2）= 3+2， 如统计A1=5的记录时 Sum（a2）= 1+6，请高手指教。谢谢。

用游标

select a.a1,isnull(a.a2,0)+(select top 1 isnull(b.a2,0) from tablename b where b.a1<a.a1 order by b.a1 desc)
from tablename a
--where a.a1=4
order by a.a1
第一条单独求，加where可以具体求某条记录，不加求全部。

/*
A1 A2
1 2
2 3
4 1
5 6
*/
create table tb(a1 int, a2 int)
insert into tb values(1,2)
insert into tb values(2,3)
insert into tb values(4,1)
insert into tb values(5,6)
select * ,a2+isnull(lasta2,0) as mysum from
(select t3.*,tb.a2 as lasta2 from
(select tb.a1,tb.a2,max(tb2.a1 )as lasta1 from tb left outer join tb as tb2 on tb.a1>tb2.a1 group by tb.a1,tb.a2) as t3
left outer join tb on t3.lasta1=tb.a1
)as tt
order by a1
drop table tb
结果是
a1 a2 lasta1 lasta2 mysum
----------- ----------- ----------- ----------- -----------
1 2 NULL NULL 2
2 3 1 2 5
4 1 2 3 4
5 6 4 1 7

select a.a1,isnull(a.a2,0)+(
case when exists(select top 1 * from tablename b where b.a1<a.a1 order by b.a1 desc) then
(select top 1 isnull(c.a2,0) from tablename c where c.a1<a.a1 order by c.a1 desc) else
0 end
) total
from tablename a
--where a.a1=4
order by a.a1
可求全部，加where单独求某条

/*
A1 A2
1 2
2 3
4 1
5 6
*/
create table tb(a1 int, a2 int)
insert into tb values(1,2)
insert into tb values(2,3)
insert into tb values(4,1)
insert into tb values(5,6)
/*
select * ,a2+isnull(lasta2,0) as mysum from
(select t3.*,tb.a2 as lasta2 from
(select tb.a1,tb.a2,max(tb2.a1 )as lasta1 from tb left outer join tb as tb2 on tb.a1>tb2.a1 group by tb.a1,tb.a2) as t3
left outer join tb on t3.lasta1=tb.a1
)as tt
order by a1
*/
-- 这是 写法2
select * ,a2+isnull(lasta2,0) as mysum from
(
select tt.* ,(select top 1 a2 from tb as t2 where t2.a1=lasta1 ) as lasta2 from
(select *,(select max(t1.a1) from tb as t1 where t1.a1<tb.a1 ) as lasta1 from tb) as tt
)as ttt
drop table tb