【转载】ROWNUM 及 ROW_NUMBER()OVER() 排序
ROWNUM →→→ROW_NUMBER()OVER()
对于rownum和row_number()over()函数并没有太大差异。
row_number()函数的基本语法为:row_number() over(order by COLUMN_NAME) ,此函数还提供了排序的功能。
如果是在分页的场合下,我们首推row_number()over()函数。
我们已员工表为例子:
--查询(薪水从高到底的)员工信息
SELECT rownum,e.* FROM emp e ORDER BY sal;
结果:
--问题:为什么Order by以后行号(ROWNUM)是乱的。
--原因:先分配了行号(ROWNUM),再根据sal排序。
分页:
<1>第一页数据
SELECT ROWNUM ,t.* FROM (
SELECT e.* FROM emp e ORDER BY e.sal DESC ) t
WHERE ROWNUM between 1 AND 3
结果:
<2>第二页数据
SELECT ROWNUM ,t.* FROM (
SELECT e.* FROM emp e ORDER BY e.sal DESC ) t
WHERE ROWNUM between 4 AND 6
结果:
结果集为空!我们不难发现,ROWNUM必须从1开始!这是由rownum的特性(没有1就不会有2,没有3……以此类推下去),就像我一哥们说:没有父亲,哪来儿子!
问题:那么可能会有疑问,如何正确显示第二页的数据呢?
有两种方法:
第一种:以上的sql语句再嵌套一个SELECT语句
SELECT k.* FROM (
SELECT ROWNUM r,t.* FROM (
SELECT e.* FROM emp e ORDER BY e.sal DESC ) t
) k
WHERE r between 4 AND 6
结果:
第二种:使用ROW_NUMBER()OVER()函数
select t2.*
from (select t.*, row_number() over(order by t.sal desc) orderNumber
from emp t
) t2
where orderNumber between 4 and 6;
结果:
两者在效率上没有任何差异,如果是对索引列进行排序的时候,row_number()over()函数有明显优势。
**转自:http://www.cnblogs.com/zhangrongqi/archive/2011/12/13/2285465.html**