oracle top分页有问题,Oracle-TOP-N和分页问题

SQL> --第一题 行号 rownum(伪列)

SQL> select rownum,empno,ename,sal

2 from emp;

ROWNUM EMPNO ENAME SAL

---------- ---------- ---------- -----

1 7369 SMITH 800

2 7499 ALLEN 1600

3 7521 WARD 1250

4 7566 JONES 2975

5 7654 MARTIN 1250

6 7698 BLAKE 2850

7 7782 CLARK 2450

8 7788 SCOTT 3000

9 7839 KING 5000

10 7844 TURNER 1500

11 7876 ADAMS 1100

ROWNUM EMPNO ENAME SAL

---------- ---------- ---------- -----

12 7900 JAMES 950

13 7902 FORD 3000

14 7934 MILLER 1300

已选择14行。

SQL> select rownum,empno,ename,sal

2 from emp

3 where rownum<=3

4 order by sal desc;

ROWNUM EMPNO ENAME SAL

---------- ---------- ---------- -----

2 7499 ALLEN 1600

3 7521 WARD 1250

1 7369 SMITH 800

SQL> /*

SQL> 注意的问题：

SQL> 1. 行号永远按照默认的顺序生成

SQL> 2. 行号只能使用< <=,不能使用> >=

SQL> */

SQL> select rownum,empno,ename,sal

2 from emp

3 order by sal desc;

ROWNUM EMPNO ENAME SAL

---------- ---------- ---------- -----

9 7839 KING 5000

13 7902 FORD 3000

8 7788 SCOTT 3000

4 7566 JONES 2975

6 7698 BLAKE 2850

7 7782 CLARK 2450

2 7499 ALLEN 1600

10 7844 TURNER 1500

14 7934 MILLER 1300

3 7521 WARD 1250

5 7654 MARTIN 1250

ROWNUM EMPNO ENAME SAL

---------- ---------- ---------- -----

11 7876 ADAMS 1100

12 7900 JAMES 950

1 7369 SMITH 800

已选择14行。

SQL> select rownum,empno,ename,sal

2 from emp

3 where rownum>=5 and rownum<=8;

未选定行

SQL> --rownum永远从1开始

分页

SQL> --第一题

SQL> select rownum,empno,ename,sal

2 from (select * from emp order by sal desc)

3 where rownum<=3;

ROWNUM EMPNO ENAME SAL

---------- ---------- ---------- -----

1 7839 KING 5000

2 7788 SCOTT 3000

3 7902 FORD 3000

SQL> select *

2 from (select rownum r,e1.*

3 from (select * from emp order by sal) e1

4 where rownum <=8

5 )

6 where r >=5;

R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- ---------- --------- ---------- -------------- ----- ---------- ----------

5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

6 7934 MILLER CLERK 7782 23-1月 -82 1300 10

7 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

8 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

SQL> --第二题

SQL> select empno,ename,sal,avgsal

2 from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d

3 where e.deptno=d.deptno and e.sal>d.avgsal;

SQL> --相关子查询

SQL> -- : 将主查询中的某个值作为参数传递给子查询

SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal

2 from emp e

3 where sal > (select avg(sal) from emp where deptno=e.deptno);

EMPNO ENAME SAL AVGSAL

---------- ---------- ----- ----------

7499 ALLEN 1600 1566.66667

7566 JONES 2975 2175

7698 BLAKE 2850 1566.66667

7788 SCOTT 3000 2175

7839 KING 5000 2916.66667

7902 FORD 3000 2175

已选择6行。

行转列函数

SQL> --wm_concat 行转列 (组函数)

SQL> select deptno,wm_concat(ename) names

2 from emp

3 group by deptno;

DEPTNO NAMES

------ --------------------------------------------------

10 CLARK,KING,MILLER

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

SQL> select count(*) Total,sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",

2 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",

3 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",

4 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"

5 from emp;

TOTAL 1980 1981 1982 1987

---------- ---------- ---------- ---------- ----------

14 1 10 1 2