问题一:获取SCOTT用户中EMP表里最早入职的前五名雇员?
SQL> select top 5 * from emp order by hiredate asc;
SQL Server中的写法,不适用于Oracle数据库。
下面也是错误的写法
SQL> select rownum,empno,ename,hiredate from emp
2 where rownum<6
3 order by hiredate asc;
正确的写法:
使用了内嵌视图,构造中间的临时查询结果,在这个结果基础上,使用rownum处理
1 select empno,ename,hiredate from (select empno,ename,hiredate from emp
2 order by hiredate asc)
3* where rownum<6
SQL> /
问题二:获取SCOTT用户中EMP表里最早入职的 第 五 名 雇员?
1 select empno,ename,hiredate from
2 (select empno,ename,hiredate from
3 (select empno,ename,hiredate from (select empno,ename,hiredate from emp
4 order by hiredate asc)
5 where rownum<6) order by hiredate desc)
6* where rownum<2
SQL> /