发布时间:2019-08-25 09:33:22编辑:auto阅读(1450)
SCOTT>select ename,sal
2 from emp e,
3 (select deptno,avg(sal) avg_sal from emp group by deptno) s
4 where e.deptno=s.deptno and e.sal>s.avg_sal;
ENAME SAL
---------- ----------
BLAKE 2850
ALLEN 1600
FORD 3000
SCOTT 3000
JONES 2975
KING 5000
6 rows selected.
SCOTT>select ename,sal from (select rownum no,e.* from emp e where rownum<=8) where no >=5 order by sal desc;
ENAME SAL
---------- ----------
SCOTT 3000
BLAKE 2850
CLARK 2450
MARTIN 1250
SCOTT>select * from (select * from emp order by sal desc) where rownum<=3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SCOTT>select * from
2 (select row_number() over(partition by deptno order by sal desc) no,ename,sal,deptno
3 from emp)
4 where no<=2
5 ;
NO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
1 KING 5000 10
2 CLARK 2450 10
1 SCOTT 3000 20
2 FORD 3000 20
1 BLAKE 2850 30
2 ALLEN 1600 30
6 rows selected.
上一篇: iptables学习(3)
下一篇: LAB_3 NAT
46502
44393
35839
33405
28094
24664
23583
18798
18066
16750
4567°
5126°
4654°
4796°
5457°
4423°
4476°
5021°
4915°
6162°