求关于通过本行比较上一行值条件，更新SQL的语句。
create table aaa1
(
aa int,
bb int,
cc int,
dd int
)
go
insert into aaa1 values(1,1,1,10)
insert into aaa1 values(1,2,2,40)
insert into aaa1 values(1,3,13,50)
insert into aaa1 values(1,5,15,30)
insert into aaa1 values(2,3,2,20)
insert into aaa1 values(2,5,5,60)
要求：aa,bb分组，组内按bb升序排列，根据cc列计算dd的列：如果cc2-cc1<5,则dd2=dd1否则数据不变(即同一组中本一行的C值比较上行的值，如果小于5，本一行dd列等于上一行列,cc2代表本行，cc1代表上一行)
需求简言之就是本行比较上一行的值得到结果。
select * from aaa1
--aabbccdd
--11110
--12240
--131350
--151530
--23220
--255 60
更新后
--结果：aabbccdd
--11110
--12210
--131350
--151550
--23220
--25520
由于数据大概有6万行，现在想到的办法是用游标，速度非常的慢，有没有其它的办法处理。 SQL?分组?比较本行上行
[解决办法]
SQL2000的方法,
create table aaa1
( aa int,
bb int,
cc int,
dd int)
insert into aaa1 values(1,1,1,10)
insert into aaa1 values(1,2,2,40)
insert into aaa1 values(1,3,13,50)
insert into aaa1 values(1,5,15,30)
insert into aaa1 values(2,3,2,20)
insert into aaa1 values(2,5,5,60)
-- 更新
select aa,bb,cc,dd,
(select count(1) from aaa1 b
where b.aa=a.aa and b.bb<=a.bb) 'rn'
into #t
from aaa1 a
update x
set x.dd=case when y.rn is null then x.dd
when (x.cc-y.cc)<5 then y.dd
else x.dd end
from #t x
left join #t y on x.aa=y.aa and x.rn=y.rn+1
update a
set a.dd=b.dd
from aaa1 a
inner join #t b on a.aa=b.aa and a.bb=b.bb and a.cc=b.cc
select * from aaa1
/*
aa bb cc dd
----------- ----------- ----------- -----------
1 1 1 10
1 2 2 10
1 3 13 50
1 5 15 50
2 3 2 20
2 5 5 20
(6 row(s) affected)
*/
drop table #t
--drop table aaa1
create table aaa1
(
aa int,
bb int,
cc int,
dd int
)
go
insert into aaa1 values(1,1,1,10)
insert into aaa1 values(1,2,2,40)
insert into aaa1 values(1,3,13,50)
insert into aaa1 values(1,5,15,30)
insert into aaa1 values(2,3,2,20)
insert into aaa1 values(2,5,5,60)
select aa,bb,cc,
case when (select top 1 a2.cc
from aaa1 a2
where a1.aa = a2.aa
and a1.bb > a2.bb
order by bb desc) is null
then dd
when cc - (select top 1 a2.cc
from aaa1 a2
where a1.aa = a2.aa
and a1.bb > a2.bb
order by bb desc) < 5
then (select top 1 a2.dd
from aaa1 a2
where a1.aa = a2.aa
and a1.bb > a2.bb
order by bb desc)
else dd
end as dd
from aaa1 a1
/*
aabbccdd
11110
12210
131350
151550
23220
25520
*/
