根据某些条件返回不同的值
mysql写过:if(表达式, A, B)
oracle只能这么写:case when 表达式 then A else B end,感觉mysql的好写多了,至于有没有性能上的问题,还没想过。
多表间的数据更新
mysql:
update t1,t2 set t1.a=t2.a where t1.id=t2.id
oracle:
update t1 set t1.a=(select t2.a from t2 where t1.id=t2.a)
where exists(select 1 from t2 where t1.id=t2.id)
案例:在多表更新中,经常存在着一个id对应多条的记录,要取出最有效的一条
第一种:
update t1 set t1.a=
(
select t2.a from t2
(select t2.id,max(t2.update_time) maxtime from t2 group by t2.id) tm
where t2.id=tm.id and t2.update_time=tm.maxtime and t2.id=t1.id
)
where exists(...)
第二种:
update t1 set t1.a=
(
select a from (select t2.*,row_number()over(order by update_time desc) rn from t2 where t1.id=t2.id) where rn=1 ;
)
where exists(...)
//todo 还有一个是关于oracle版本问题的
查询指定记录的上一条下一条:
mysql:
//todo
oracle:
select c.p from (select id, lag(id,1,0) over (order by update_time) as p from t1) c where c.id='111';
select c.n from (select id, lead(id,1,0) over (order by update_time) as p from t1) c where c.id='111';