sql3
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.
相关文章