--解决top-n分析问题 使用伪列rownum
SQL> --rownum 行号 伪列
SQL> select rownum,empno,ename,sal 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> 关于rownum
SQL> 1、rownum永远按照默认的顺序生成
SQL> 2、rownum只能使用< <=；不能使用> >=
SQL> */
SQL> select rownum,empno,ename,sal from emp 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> --第一题：找出员工表中工资最高的前三名
SQL> select rownum,empno,ename,sal
2 from (select * from emp order by sal desc) --from后产生的是一个新的表
3 where rownum<=3;
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
1 7839 KING 5000
2 7788 SCOTT 3000
3 7902 FORD 3000
SQL> --2、rownum只能使用< <=；不能使用> >=
--原因：oracle是一个行式数据库，取第一行，再取第二行，依次进行，rownum 永远从1开始
SQL> --分页
SQL> select rownum,empno,ename,sal from emp
2 where rownum>=5 and rownum<=8;
未选定行
SQL> --rownum 永远从1开始
SQL> select rownum,empno,ename,sal from emp
2 where rownum>=5;
未选定行
SQL> ed
已写入 file afiedt.buf
1 select rownum,empno,ename,sal from emp
2* where rownum<=8
SQL> /
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
已选择 8 行。
--正确答案
SQL> select *
2 from (select rownum r,e1.* --r是e1表的伪列 ,在e2表中会生成一个新的伪列，与e1表的伪列同名
3 from (select * from emp order by sal) e1
4 where rownum <=8
5 ) e2
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> /*
--> oracle中的三种表：标准表、索引表
--> 临时表:1 --temporary
--> 1. create global temporary table ****
--> 2. 自动创建临时表的一个典型例子：排序
--order by会产生一张临时表，临时表中的数据就是就是临时表的结果，但是行号还是不变
--> 临时表的特点：当事务或者会话结束的时候，表中的数据自动删除*/
SQL> */
SP2-0042: 未知命令 "*/" - 其余行忽略。
SQL> create global temporary table temptest1
2 (tid number ,tname varchar2(20));
表已创建。
SQL> insert into temptest1 values(1,
2 'Tom');
已创建 1 行。
SQL> insert into temptest1 values(2,'Mary');
已创建 1 行。
SQL> select * from temptest1;
TID TNAME
---------- --------------------
1 Tom
2 Mary
SQL> commit;
提交完成。
SQL> select * from temptest1;
未选定行
SQL> host cls
SQL> --第二题：找出员工表中薪水大于本部门平均薪水的员工
SQL> select e.empno,e.ename,e.sal,d.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;
EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7698 BLAKE 2850 1566.66667
7499 ALLEN 1600 1566.66667
7902 FORD 3000 2175
7788 SCOTT 3000 2175
7566 JONES 2975 2175
7839 KING 5000 2916.66667
已选择 6 行。
--第二题使用相关子查询
SQL> --相关子查询：select e.empno,e.ename,e.sal,d.avgsal
SQL> from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
SP2-0734: 未知的命令开头 "from emp e..." - 忽略了剩余的行。
SQL> host cls
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> host cls
SQL> -- 第三题：统计每年入职的员工个数
SQL> select hiredate from emp;
HIREDATE
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81
28-9月 -81
01-5月 -81
09-6月 -81
19-4月 -87
17-11月-81
08-9月 -81
23-5月 -87
HIREDATE
--------------
03-12月-81
03-12月-81
23-1月 -82
已选择 14 行。
SQL> /*
SQL> select count(*) Total,
SQL>
SQL> sum(if 是81年 then +1 else +0) "1981",
SQL>
SQL> from emp;
SQL>
SQL> HIREDATE count81 number := 0;
SQL> -----------------------------
SQL> 17-12月-80 0
SQL> 20-2月 -81 1
SQL> 22-2月 -81 1
SQL> 02-4月 -81 1
SQL> 28-9月 -81 1
SQL> 01-5月 -81 1
SQL> 09-6月 -81 1
SQL> 19-4月 -87 0
SQL> 17-11月-81 1
SQL> 08-9月 -81 1
SQL> 23-5月 -87 0
SQL> 03-12月-81 1
SQL> 03-12月-81 1
SQL> 23-1月 -82 0
SQL> ---------------------------
SQL> 10
SQL>
SQL> */
SQL> host cls
SQL> --行转列
SQL> -- wm_concat(varchar2) 多行函数
SQL> select deptno,wm_concat(ename) nameslist
2 from emp
3 group by deptno;
DEPTNO
----------
NAMESLIST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
CLARK,KING,MILLER
20
SMITH,FORD,ADAMS,SCOTT,JONES
30
ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
SQL> col NAMESLIST for a60
SQL> select deptno,wm_concat(ename) nameslist
2 from emp
3 group by deptno;
DEPTNO NAMESLIST
---------- ------------------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
SQL> spool off
top-n问题，相关子查询，from后接select
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
