Oracle高级查找

浏览数：19 / 时间：2015年06月12日

1.rownum:rownum是一个伪列，需要在数据取出来后，rownum才会有值，因此在分页查找时，需要进行嵌套查询。

select sal,ename from
 (select rownum as rn,sal,ename from    
   (select sal,ename from emp where sal is not null order by sal) x
 where rownum<10)
where rn>6

采用分析函数也是可以实现一次嵌套

select rn,ename,sal from   
   (select rownum() over(order by sal) as rn,sal,ename where sal is not null ) x
where rn between 6 and 10

但是由于分析函数的影响，有些索引可能失效，建议大家采用第一种写法。

隔行返回数据,对伪列求余即可,MOD(rn,X)

2.Merge:高效的表更新处理

Merge inot test a
using
 (select rowid as rid,nbr*100+rownum() over(partition by nbr order by rowid) as nnbr from test) b
on(a.rowid=b.rid)
when matched then
update set a.nbr = b.nnbr;

大家猜猜 test表扫描了几次?????

3.将表中某些列，排列组合去重

step1:列转行

select * from test unpivot(b2 for b3 in(t1,t2,t3)

unpivot行列转置非常牛逼的一个方法

step2:按照值排序并合并

with x1 as 
  (select * from test unpivot(b2 for b3 in(t1,t2,t3)),
select id,listagg(b2,‘,‘) within group (order by b2) as b
from x1
group by id;

listagg实现分组后，值排序后，值按逗号拼接

step3:去除重复

with x1 as 
  (select * from test unpivot(b2 for b3 in(t1,t2,t3)),
x2 as
( select id,listagg(b2,‘,‘) within group (order by b2) as b 
  from x1
  group by id)
select id,b,row_number() over(partition by b order by id) as sn from x2