Oracle中Topn的最佳实践
一条sql搞定排名前五和倒数前五名。有并列排名时考虑用rank() over(),数据量比较大时,wm_concat有bug,会出错,11G版本建议用listagg代替。
select
owner,
wm_concat(case when rndesc<=5 then object_id end) topdesc,
wm_concat(case when rnasc<=5 then object_id end) topasc
from (
select * from (
select a.owner,
       a.OBJECT_ID,
       row_number() over(partition by owner order by a.OBJECT_ID desc) rndesc,
       row_number() over(partition by owner order by a.OBJECT_ID ) rnasc
  from dba_objects a
) where rndesc<=5 or rnasc<=5
) b group by owner



topN.png

0

热门评论