- SQL code
update (select /*+ BYPASS_UJVC */t1.f1, t1.F4, t1.f5, t1.f10, t2.f10 b, t1.f21 a, (t1.f5-t2.f5) from A t1,(select f1,F4,f10,f5 from A where f10<>0 and f10 is not null and isvalid=1) t2where t1.f5>t2.f5 and t1.f1=t2.f1and t1.F4=t2.F4having t2.f5.f5=(select min(t1.f5d-t2.f5) from t1,(select f1,F4,f10,f5 from A where f10<>0 and f10 is not null and isvalid=1) t2)set a=b ;
------解决方案--------------------
这么傻逼的语句，让人家帮你重组啊！
------解决方案--------------------
使用LAG+开窗函数，可以查到你要求的数据。UPDATE语法不熟，你自己搞定吧。
select name,what,place,tim,money,lag(money)over(partition by name,place order by name,tim) as last from
(
select '1' as name,'1' as what,'超市' as place,sysdate as tim, 12 as money from dual
union all
select '1' ,'1' ,'超市',sysdate-1, 15 from dual
union all
select '1' ,'0' ,'超市',sysdate+1, 25 from dual
union all
select '2' ,'1' ,'学校',sysdate, 35 from dual
union all
select '2' ,'1' ,'学校',sysdate-1, 45 from dual
union all
select '2' ,'1' ,'学校',sysdate-2, 55 from dual)
where what = '1'
--结果
NAME WHAT PLACE TIM MONEY LAST
---- ---- ----- ------------------------- ---------------------- ----------------------
1 1 超市 2012-04-12 16:47:30 15
1 1 超市 2012-04-13 16:47:30 12 15
2 1 学校 2012-04-11 16:47:30 55
2 1 学校 2012-04-12 16:47:30 45 55
2 1 学校 2012-04-13 16:47:30 35 45