来优化一下解决方案

来优化一下

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

查看更多 下一篇