首页 > 代码库 > ORACLE分页SQL语句
ORACLE分页SQL语句
一个非常好的ORACLE的分页SQL语句 select * from (select my_table.*, rownum as my_rownum from ( select yhbh, yhmc from yysf_tb_yonghxx order by yhbh) my_table where rownum <20 ) where my_rownum>=10------------------------------------其它几种分页实现:1.根据ROWID来分select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid fromt_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) wherern>9980;执行时间0.1秒其中t_xiaoxi为表名称,cid为表的关键字段,取按CID降序排序后的第9981-9999条记录,t_xiaoxi表有70000多条记录个人感觉1的效率最好,3次之,2最差 oracle如何返回指定行数之间的查询结果如何返回指定行数之间的查询结果,以实现web记录分页,在Oracle中有许多的方法,这里仅仅列出了4种,希望能对大家有所帮助,大家可以根据不同需要选择下面的script 1)select ... where rownum < 50 minus select ... where rownum < 30 这个方法因为用到了minus操作符,所以速度会受影响。 2) SELECT results.* FROM ( SELECT t2.*, rownum rownumber FROM ( SELECT t.* FROM mv_table t WHERE ORDER BY col1) t2) results WHERE results.rownumber BETWEEN 30 and 50 ORDER BY col1 这个方法是从一个论坛上看到的,没有亲自测试过 3) 定义cursor x, 2.fetch x a,b,c; loop ...... end loop; 其中用两个循环变量和一个FLAG变量,分别表示,当前的记录数,属于第几页的, 及第一页面。 ps; j:=to_number(kafyf); i:=1; open cx; loop fetch cx into col1,col2,col3,col4,col5,col6; if cx%NOTFOUND then exit; end if; if i>=j then htp.tableRowOpen; htp.tableData(col1); htp.tableData(col2); htp.tableData(col4); htp.tableData(col5); htp.tableData(col6); htp.tableData(col3); htp.tableRowClose; i:=i+1; if i=j+10 then l:=1; exit; end if; else i:=i+1; end if; end loop; close x; 该方法是名叫‘淼’的网友写的script,他用到了Oracle web2kit中的OWA_UTIL package。 4)How can one page forward and backwards through a table? Externalize ROWNUM by implementing queries like this: SELECT ... FROM (SELECT ROWNUM rnum, ... FROM ...) WHERE rnum BETWEEN :low AND :high AND rownum <(:high :low + 1); where :low and :high are dynamically generated values depending on which result page the user is viewing. Typically, they are used to show "Next 15 matches", "Previous 15 matches" links at the bottom of each page.
Oracle高效的SQL语句之分析函数汇总分类: Oracle 2007-11-08 08:55 371人阅读 评论(0) 收藏 举报oraclesqltableinsertdate报表小毛头的BLOG[Oracle]高效的SQL语句之分析函数(一)--sum() 实际应用中我们可以通过sum()统计出组中的总计或者是累加值,具体示例如下:1.创建演示表create table empasselect * from scott.emp;alter table empadd constraint emp_pkprimary key(empno);create table deptasselect * from scott.dept;alter table deptadd constraint dept_pkprimary key(deptno);2. sum()语句如下:select deptno, ename, sal, --按照部门薪水累加(order by改变了分析函数的作用,只工作在当前行和前一行,而不是所有行) sum(sal) over (partition by deptno order by sal) CumDeptTot, sum(sal) over (partition by deptno) SalByDept, --统计一个部门的薪水 sum(sal) over (order by deptno,sal) CumTot, --所有雇员的薪水一行一行的累加 sum(sal) over () TotSal --统计总薪水 from emp order by deptno, sal3. 结果如下:10 MILLER 1300.00 1300 8750 1300 2902510 CLARK 2450.00 3750 8750 3750 2902510 KING 5000.00 8750 8750 8750 2902520 SMITH 800.00 800 10875 9550 2902520 ADAMS 1100.00 1900 10875 10650 2902520 JONES 2975.00 4875 10875 13625 2902520 SCOTT 3000.00 10875 10875 19625 2902520 FORD 3000.00 10875 10875 19625 2902530 JAMES 950.00 950 9400 20575 2902530 WARD 1250.00 3450 9400 23075 2902530 MARTIN 1250.00 3450 9400 23075 2902530 TURNER 1500.00 4950 9400 24575 2902530 ALLEN 1600.00 6550 9400 26175 2902530 BLAKE 2850.00 9400 9400 29025 29025[Oracle]高效的SQL语句之分析函数(二)--max()如果我们按照示例想得到每个部门薪水值最高的雇员的纪录,可以有四种方法实现:先创建示例表create table empasselect * from scott.emp;alter table empadd constraint emp_pkprimary key(empno);create table deptasselect * from scott.dept;alter table deptadd constraint dept_pkprimary key(deptno);方法1.emp中的每一行都会进行max比较,费时select * from emp emp1 where emp1.sal=(select max(emp2.sal) from emp emp2 where emp2.deptno=emp1.deptno)方法2.先子查询查找出max sal,然后与emp表相关联,如果逻辑复杂会产生较多代码 select * from emp emp1,(select deptno,max(sal) maxsal from emp emp2 group by emp2.deptno) emp3 where emp1.deptno=emp3.deptno and emp1.sal=emp3.maxsal方法3.使用max分析函数select deptno,maxsal,empno from( select max(sal) over (partition by deptno) maxsal,emp.* from emp) emp2 where emp2.sal=emp2.maxsal方法4.使用dense_rank分析函数,如果一个部门可能存在多笔最大薪水,就不能使用row_number()分析函数select deptno,sal,empno from( select emp.*,DENSE_RANK() over (partition by deptno order by sal desc) rownumber from emp) emp2 where rownumber=1 结果如下:10 5000.00 783920 3000.00 778820 3000.00 790230 2850.00 7698 [Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank() 有些时候我们希望得到指定数据中的前n列,示例如下:得到每个部门薪水最高的三个雇员:先创建示例表create table empasselect * from scott.emp;alter table empadd constraint emp_pkprimary key(empno);create table deptasselect * from scott.dept;alter table deptadd constraint dept_pkprimary key(deptno);先看一下row_number() /rank()/dense_rank()三个函数之间的区别 select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2结果如下:10 5000.00 7839 1 1 110 2450.00 7782 2 2 210 1300.00 7934 3 3 320 3000.00 7788 1 1 120 3000.00 7902 2 1 120 2975.00 7566 3 3 220 1100.00 7876 4 4 320 800.00 7369 5 5 430 2850.00 7698 1 1 130 1600.00 7499 2 2 2取每个部门的薪水前三位雇员:select t.deptno,t.rank,t.sal from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) twhere t.rank<=3结果如下:10 1 5000.0010 2 2450.0010 3 1300.0020 1 3000.0020 1 3000.0020 3 2975.0030 1 2850.0030 2 1600.0030 3 1500.00如果想输出成deptno sal1 sal2 sal3这种类型的格式步骤一(decode):select t.deptno,decode(row_number,1,sal) sal1,decode(row_number,2,sal) sal2,decode(row_number,3,sal) sal3 from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) twhere t.rank<=3结果如下:10 5000 10 2450 10 130020 3000 20 3000 20 297530 2850 30 1600 30 1500步骤二(使用聚合函数去除null,得到最终结果):select t.deptno,max(decode(row_number,1,sal)) sal1,max(decode(row_number,2,sal)) sal2,max(decode(row_number,3,sal)) sal3 from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) twhere t.rank<=3group by t.deptno 结果如下:10 5000 2450 130020 3000 3000 297530 2850 1600 1500 [Oracle]高效的SQL语句之分析函数(四)--lag()/lead() 有时候报表上面需要显示该笔操作的上一步骤或者下一步骤的详细信息,这个时候可以按照下面的做法:先创建示例表:-- Create tablecreate table LEAD_TABLE( CASEID VARCHAR2(10), STEPID VARCHAR2(10), ACTIONDATE DATE)tablespace COLM_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );insert into LEAD_TABLE values(‘Case1‘,‘Step1‘,to_date(‘20070101‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step2‘,to_date(‘20070102‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step3‘,to_date(‘20070103‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step4‘,to_date(‘20070104‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step5‘,to_date(‘20070105‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step4‘,to_date(‘20070106‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step6‘,to_date(‘20070101‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case1‘,‘Step1‘,to_date(‘20070201‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case2‘,‘Step2‘,to_date(‘20070202‘,‘yyyy-mm-dd‘));insert into LEAD_TABLE values(‘Case2‘,‘Step3‘,to_date(‘20070203‘,‘yyyy-mm-dd‘));commit; 每一条记录都能连接到上/下一行的内容select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,lag(stepid) over (partition by caseid order by actiondate) prestepid,lag(actiondate) over (partition by caseid order by actiondate) preactiondatefrom lead_table结果如下:Case1 Step1 2007-1-1 Step2 2007-1-2 Case1 Step2 2007-1-2 Step3 2007-1-3 Step1 2007-1-1Case1 Step3 2007-1-3 Step4 2007-1-4 Step2 2007-1-2Case1 Step4 2007-1-4 Step5 2007-1-5 Step3 2007-1-3Case1 Step5 2007-1-5 Step4 2007-1-6 Step4 2007-1-4Case1 Step4 2007-1-6 Step6 2007-1-7 Step5 2007-1-5Case1 Step6 2007-1-7 Step4 2007-1-6Case2 Step1 2007-2-1 Step2 2007-2-2 Case2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1Case2 Step3 2007-2-3 Step2 2007-2-2 还可以进一步统计一下两者的相差天数select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from (select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,lag(stepid) over (partition by caseid order by actiondate) prestepid,lag(actiondate) over (partition by caseid order by actiondate) preactiondatefrom lead_table) 结果如下:Case1 Step1 2007-1-1 2007-1-2 1Case1 Step2 2007-1-2 2007-1-3 1Case1 Step3 2007-1-3 2007-1-4 1Case1 Step4 2007-1-4 2007-1-5 1Case1 Step5 2007-1-5 2007-1-6 1Case1 Step4 2007-1-6 2007-1-7 1Case1 Step6 2007-1-7 Case2 Step1 2007-2-1 2007-2-2 1Case2 Step2 2007-2-2 2007-2-3 1Case2 Step3 2007-2-3
ORACLE分页SQL语句
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。