• Oracle分页总汇


    Oracle分页总汇
     
    select * from
    (select a.*,rownum row_num from
    (select * from mytable t order by t.id desc) a
    where rownum<=10
    ) b where b.row_num >= 1 order by u_age desc;    
     
    select * from 
     
    (select a.*,rownum row_num from 
     
    (select * from mytable t order by t.id desc) a
     
    ) b where b.row_num between 1 and 10 
     
    .1.根据ROWID来分
     
    select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from 
     
    t_xiaoxi  order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
    执行时间0.03秒
     
    2.按分析函数来分
     
    select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
    执行时间1.01秒
     
    3.按ROWNUM来分
     
    select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where rn>9980;
    执行时间0.1秒
    其中t_xiaoxi为表名称,cid为表的关键字段,取按CID降序排序后的第9981-9999条记录,t_xiaoxi表有70000多条记录
    个人感觉1的效率最好,3次之,2最差
     
    --1:无ORDER BY排序的写法。(效率最高) 
    --(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!) 
     
    SELECT * 
      FROM (Select ROWNUM AS ROWNO, T.* 
               from k_task T 
              where Flight_date between to_date('20060501', 'yyyymmdd') and 
                    to_date('20060731', 'yyyymmdd') 
                AND ROWNUM <= 20) TABLE_ALIAS 
    WHERE TABLE_ALIAS.ROWNO >= 10; 
     
    --2:有ORDER BY排序的写法。(效率最高) 
    --(经过测试,此方法随着查询范围的扩大,速度也会越来越慢哦!) 
     
    SELECT * 
      FROM (SELECT TT.*, ROWNUM AS ROWNO 
               FROM (Select t.* 
                        from k_task T 
                       where flight_date between to_date('20060501', 'yyyymmdd') and 
                             to_date('20060531', 'yyyymmdd') 
                       ORDER BY FACT_UP_TIME, flight_no) TT 
              WHERE ROWNUM <= 20) TABLE_ALIAS 
    where TABLE_ALIAS.rowno >= 10;
     
    --3:无ORDER BY排序的写法。(建议使用方法1代替) 
    --(此方法随着查询数据量的扩张,速度会越来越慢哦!) 
    --4:有ORDER BY排序的写法.(建议使用方法2代替)
     
    SELECT * 
      FROM (Select ROWNUM AS ROWNO, T.* 
               from k_task T 
              where Flight_date between to_date('20060501', 'yyyymmdd') and 
                    to_date('20060731', 'yyyymmdd')) TABLE_ALIAS 
    WHERE TABLE_ALIAS.ROWNO <= 20 
       AND TABLE_ALIAS.ROWNO >= 10; 
    --TABLE_ALIAS.ROWNO  between 10 and 100; 
     
    --(此方法随着查询范围的扩大,速度会越来越慢哦!) 
     
    SELECT * 
      FROM (SELECT TT.*, ROWNUM AS ROWNO 
               FROM (Select * 
                        from k_task T 
                       where flight_date between to_date('20060501', 'yyyymmdd') and 
                             to_date('20060531', 'yyyymmdd') 
                       ORDER BY FACT_UP_TIME, flight_no) TT) TABLE_ALIAS 
    where TABLE_ALIAS.rowno BETWEEN 10 AND 20; 
     
    --5另类语法。(有ORDER BY写法) 
    --(语法风格与传统的SQL语法不同,不方便阅读与理解,为规范与统一标准,不推荐使用。) 
     
    With partdata as( 
      SELECT ROWNUM AS ROWNO, TT.*  FROM (Select * 
                      from k_task T 
                     where flight_date between to_date('20060501', 'yyyymmdd') and 
                           to_date('20060531', 'yyyymmdd') 
                     ORDER BY FACT_UP_TIME, flight_no) TT 
       WHERE ROWNUM <= 20) 
        Select * from partdata where rowno >= 10; 
     
    --6另类语法 。(无ORDER BY写法) 
     
    With partdata as( 
      Select ROWNUM AS ROWNO, T.* 
        From K_task T 
       where Flight_date between to_date('20060501', 'yyyymmdd') and 
             To_date('20060531', 'yyyymmdd') 
         AND ROWNUM <= 20) 
        Select * from partdata where Rowno >= 10; 
    

      

  • 相关阅读:
    散户如何战胜专业投资机构
    机器学习总结
    进程线程及堆栈关系的总结
    线程堆栈是如何增长的
    Ubuntu下CodeBlocks控制台程序中文显示乱码解决问题
    jdk 安装
    python文件运行报错:Error: Please select a valid Python interpreter
    python 解释器安装
    allure在pycharm下运行出现以下乱码的提示 解决方案
    pytest 测试环境框架搭建
  • 原文地址:https://www.cnblogs.com/myhappylife/p/4535298.html
Copyright © 2020-2023  润新知