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 aupdate 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+1update 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.ccselect * 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 aaa1create 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 /* 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 */
呵呵,修改一下,加了一个update语句: --drop table aaa1create 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) --更新数据 update aaa1 set dd = ( 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 where a1.aa = aaa1.aa and a1.bb = aaa1.bb and a1.cc = aaa1.cc ) --查询数据,发现字段dd已经更新 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 */
需求简言之就是本行比较上一行的值得到结果。
( 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 aupdate 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+1update 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.ccselect * 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
(
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
/*
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
*/
--drop table aaa1create 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)
--更新数据
update aaa1
set dd =
(
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
where a1.aa = aaa1.aa and
a1.bb = aaa1.bb and
a1.cc = aaa1.cc
)
--查询数据,发现字段dd已经更新
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
*/