rownum sql值获取一个值_rownum 与 rownumber（）函数 学习笔记

1、查询第几行的记录select sal from emp where rownum=1; //查询得到第一行记录select sal from emp where rownum=5; //不可以查询到第五行记录，因为rownum总是从1开始查询的，故这种方式不可以直接得到第几行的记录。若想得到第五行记录，应采用如下方式：select r,sal from (select rownum r,sal from emp) where r= 5;

2、用来获取前几行的记录，即小于某值的记录。

例：查询前四行的记录select rownum,sal from emp where rownum<5;

SQL> select * from emp1 where rownum < = 10;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH      CLERK      7902 1980/12/17     800.00               20

7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

7839 KING       PRESIDENT       1981/11/17    5000.00               10

7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

3、用来获取后几行的记录，即大于某值的记录。

例：查询第五行之后的记录

select r,sal from (select rownum r,sal from emp ) where r>5;

4、查询一个范围内的记录。如查询第三行到底八行的记录：select r,sal from (select rownum r,sal from emp) where r>=3 and r<=8;

5、rownum与排序

select rownum,sal from emp order by sal;从这条语句的运行结果你可以发现，rownum不是从1一次增大，而是乱的，实际上这些rownum是指每条记录未排序之前所处的行数，当然这不是我们想要的结果，

那么如何得到rownum也是顺序排列的查询结果呢？

这就需要先对原纪录排序，然后从新的顺序中提取出rownum和想要的记录内容。如：

select rownum,sal from (select * from emp order by sal) ;

select rownum, sal from (select * from emp order by sal ) where rownum <5;

select r, sal from (select rownum r,sal from (select * from emp order by sal )) where r >5;

select r, sal from (select rownum r,sal from (select * from emp order by sal ))where r >5 and r < 10;注意事项1.--10g及10g之后才可以使用rownum=1SELECT * FROM user_objectsWHERE /*object_id <100AND*/ ROWNUM = 1;--之前的版本SELECT * FROM user_objectsWHERE object_id <100AND ROWNUM <= 1;

2.ROWNUM 和Order BY

在使用ROWNUM 时，只有当Order By 的字段是主键时，查询结果才会先排序再计算ROWNUM：

g3e_ano是主键

SELECT g3e_ano,g3e_username FROM g3e_attribute WHERE ROWNUM <= 5 ORDER BY g3e_ano;

1        备注

1002    组件序号

1008    组件序号

1009    组件序号

1010    组件序号

--以下查询因为ORDER BY的g3e_username不是主键，所以执行时是先线取出该表的6条数据，再对g3e_username排序

SELECT g3e_ano,g3e_username FROM g3e_attribute WHERE ROWNUM <= 5 ORDER BY g3e_username;

111003    设施特征唯一号

113203    设施特征唯一号

50110      设施特征唯一号

1510103    设施特征唯一号

112003    设施特征唯一号

--如果需要对非主键字段先排序再去取前n 条数据，可以通过子查询的方式实现：

select g3e_ano, g3e_username

from (select g3e_ano, g3e_username

from g3e_attribute

order by g3e_username)

where rownum <= 5;

--每页按10条记录输出(如果被排序的字段有重复值，使用rownum会出现一个问题)：

--观察下面两个语句的输出结果会发现其中55461451和55461209是在两个查询中都出现了。而fid在表中都是唯一记录的，

--说明这个输出结果是错误的

错误原因：SORT (ORDER BY STOPKEY)这种快速排序方法由于是根据数据分组来选择数据的，不是根据整个表的数据进行排序，所以N

值不同，数据的分组也不同，导致结果在数据的排序字段值都相等时，输出结果的顺序就会因为N 值不同而不同。

SELECT *

FROM (SELECT ROWNUM r, a.*

FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a

WHERE ROWNUM <= 10)

WHERE r >= 1;

1       王家宅    55461079

2       王家宅    55461206

3       王家宅    55461207

4       王家宅    55461253

5       王家宅    55461246

6       王家宅    55461209

7       王家宅    55461783

8       王家宅    55461646

9       王家宅    55461586

10     王家宅    55461451

SELECT *

FROM (SELECT ROWNUM r, a.*

FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a

WHERE ROWNUM <= 20)

WHERE r >= 11;

11       王家宅    56990485

12       王家宅    56990368

13       王家宅    56981862

14       王家宅    56981861

15       王家宅    56981807

16       王家宅    56981806

17       王家宅    56981801

18       王家宅    55461646

19       王家宅    55461451

20       王家宅    55461209

解决办法：

1、让查询计划避免“SORT (ORDER BY STOPKEY)”，采用“SORT (ORDER BY)”，使数据排序不受ROWNUM 的影响。

但这样会使所有数据都做排序：

SELECT *

FROM (SELECT a.*, ROWNUM r

FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a)

WHERE r <= 10

AND r >= 1;

SELECT *

FROM (SELECT a.*, ROWNUM r

FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a)

WHERE r <= 20

AND r >= 11;

2、在排序时，加上一个或多个字段(如主键字段、ROWID)，使排序结果具有唯一性：

SELECT *

FROM (SELECT ROWNUM r, a.*

FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name,g3e_fid) a

WHERE ROWNUM <= 10)

WHERE r >= 1;

SELECT *

FROM (SELECT ROWNUM r, a.*

FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name,g3e_fid) a

WHERE ROWNUM <= 20)

WHERE r >= 11;

3、对排序字段建立索引，并强制使用索引。这样就能利用索引已经建立好的排序结果：

CREATE INDEX idx_b$l_interest_info_name ON b$l_interest_info(name);

ALTER INDEX idx_b$l_interest_info_name REBUILD;

SELECT *

FROM (SELECT ROWNUM r, a.*

FROM (SELECT /*+index(a idx_b$l_interest_info_name)*/

name, g3e_fid

FROM b$l_interest_info a

WHERE a.name IS NOT NULL

ORDER BY name) a

WHERE ROWNUM <= 10)

WHERE r >= 1;

SELECT *

FROM (SELECT ROWNUM r, b.*

FROM (SELECT /*+index(a idx_b$l_interest_info_name)*/

a.name, a.g3e_fid

FROM b$l_interest_info a

WHERE a.name IS NOT NULL

ORDER BY a.name) b

WHERE ROWNUM <= 20)

WHERE r >= 11;row_number()

1、row_number() over (order by col_1[,col_2 ...])

按照col_1[,col_2 ...]排序,返回排序后的结果集，并且为每一行返回一个不相同的值。

SQL> select empno,ename,sal,row_number() over (order by sal desc) as seq from emp1;

EMPNO ENAME            SAL        SEQ

----- ---------- --------- ----------

0                                        1

7839 KING         5000.00          2

7902 FORD         3000.00          3

7788 SCOTT        3000.00          4

7566 JONES        2975.00          5

7698 BLAKE        2850.00          6

7782 CLARK        2450.00          7

7499 ALLEN        1600.00          8

7844 TURNER       1500.00          9

7934 MILLER       1300.00         10

7521 WARD         1250.00         11

7654 MARTIN       1250.00         12

7876 ADAMS        1100.00         13

7900 JAMES         950.00         14

7369 SMITH         800.00         15

2、row_number() over (partition by col_n[,col_m ...]order by col_1[,col_2 ...])

先按照col_n[,col_m ...进行分组，再在每个分组中按照col_1[,col_2 ...]进行排序(升序)，最后返回排好序后的结果集

oracle中row_number()实例

1.使用row_number()函数进行编号，如select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer原理：先按psd进行排序，排序完后，给每条数据进行编号。

2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3、统计每一个客户最近下的订单是第几次下的订单。

with tabs as

(  select ROW_NUMBER() over(partition by customerID  order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order  )

select MAX(rows) as '下单次数',customerID from tabs group by customerID

4、在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。

select   ROW_NUMBER() over(partition by customerID  order by insDT) as rows,

customerID,totalPrice, DID

from OP_Order where insDT>'2011-07-22'

以上代码是先执行where子句，执行完后，再给每一条记录进行编号。

row_number()与rownum的区别

使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，

而row_number()在包含排序从句后是先排序再计算行号码。