如何获取雇员表中薪水最高的前五人?
select * from(select * from emp order by sal desc) where rownum<6
如何获取雇员表中薪水排名第5的雇员信息
select * from(select * from emp order by sal desc) where rownum=5;
查询不出结果,同样rownum>=5查询第5名之后的也失败的。
注意:这里的rownum只能小于,不能大于等于。
原理是因为rownum是一个每一次都要从1开始排列的伪列,于是rownum>=n.
方法一:最简单的查询,第五条到第十条
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT *
FROM table_name) a
WHERE ROWNUM <= 10)
WHERE rn >= 5
只有一层嵌套,一个子查询,就是子查询得到的新表再次被查询,关键的一步就是rownum rn,而且不要忘了
a.* ,不然没有完整数据。
方法二:上面的没有排序,这里进行排序,因为要先排序才能查询,所以表越大,效率越慢
select * from
(select rownum rm,e.* from emp e order by sal desc)
where rm>=5 and rm<=10
方法三:分页函数方法
select * from
(select emp.*,row_number() over(order by sal desc) rank from emp)
where rank>=6 and rank<=10;
select * from
(select e.*,rownum as rn from
(select * from emp order by sal desc)e )ee
where ee.rn>=5 and ee.rn<=10
方法四:海量数据查询
select * from(select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum<=10)
where rn>=6;
分解步骤
第一步:
select * from emp order by sal desc;
第二步:
select e.*,rownum rn from(select * from emp order by sal desc) e
第三步:
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum<=10
第四步:
select * from
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum<=10)
where rn>=5
第五步:
with ee as (select e.*,rownum rn from
(select * from emp order by sal desc )e)
select ee.* from ee where ee.rn>=5 and ee.rn<=10;
重点:起别名必须查询出数据表才行,而查询添加用还不存在的条件去判断,自然失败。
所以才rownum和别名的使用区别