-- Oracle 奇怪的 row_number() 函数，大家来分析一下..........--

Question

--------- Oracle 奇怪的 row_number() 函数，大家来分析一下..........-- 查询一：直接查询(无子查询时)scott@SZTYORA> select xh, xm,xh,zxf,row_number() over(order by zxf) rn from xs;XH           XM                                       XH                  ZXF         RN
------------ ---------------------------------------- ------------ ---------- ----------
061202       王林                                     061202               40          1
061220       吴薇华                                   061220               42          2
061218       孙严                                     061218               42          3
061216       孙祥欣                                   061216               42          4
061221       刘燕敏                                   061221               42          5
061206       李计                                     061206               42          6
061201       王敏                                     061201               42          7
061204       马琳琳                                   061204               42          8
061203       王玉民                                   061203               42          9
061210       李红庆                                   061210               44         10
061241       罗林琳                                   061241               44         11
061113       严红                                     061113               48         12
061110       张蔚                                     061110               50         13
061109       张强民                                   061109               50         14
061106       李方方                                   061106               50         15
061101       王林                                     061101               50         16
061111       赵琳                                     061111               50         17
061104       韦严平                                   061104               50         18
061103       王燕                                     061103               50         19
061102       程明                                     061102               50         20
061108       林一帆                                   061108               52         21
061107       李明                                     061107               54         22已选择22行。--------------------------------------------------------------------------------------------- 查询二：有子查询(但外层查询无条件时，此时还跟查询一结果一致)scott@SZTYORA> select * from (
  2  select ascii(xm) as asc_xm, xm,xh,zxf,row_number() over(order by zxf) rn from xs ) t;    ASC_XM XM                                       XH                  ZXF         RN
---------- ---------------------------------------- ------------ ---------- ----------
  15175307 王林                                     061202               40          1
  15044788 吴薇华                                   061220               42          2
  15052185 孙严                                     061218               42          3
  15052185 孙祥欣                                   061216               42          4
  15042712 刘燕敏                                   061221               42          5
  15113614 李计                                     061206               42          6
  15175307 王敏                                     061201               42          7
  15313324 马琳琳                                   061204               42          8
  15175307 王玉民                                   061203               42          9
  15113614 李红庆                                   061210               44         10
  15187351 罗林琳                                   061241               44         11
  14989477 严红                                     061113               48         12
  15056032 张蔚                                     061110               50         13
  15056032 张强民                                   061109               50         14
  15113614 李方方                                   061106               50         15
  15175307 王林                                     061101               50         16
  15250869 赵琳                                     061111               50         17
  15310758 韦严平                                   061104               50         18
  15175307 王燕                                     061103               50         19
  15181963 程明                                     061102               50         20
  15113879 林一帆                                   061108               52         21
  15113614 李明                                     061107               54         22已选择22行。已用时间:  00: 00: 00.11--------------------------------------------------------------------------------------------- 查询三：有子查询(但外层查询有条件时，此时查询结果 RN 发生了变化，为什么呢？)scott@SZTYORA> select * from (
  2  select ascii(xm) as asc_xm, xm,xh,zxf,row_number() over(order by zxf) rn from xs ) t
  3  where t.rn>=10 and t.rn<=20;    ASC_XM XM                                       XH                  ZXF         RN
---------- ---------------------------------------- ------------ ---------- ----------
  15113614 李红庆                                   061210               44         10
  15187351 罗林琳                                   061241               44         11
  14989477 严红                                     061113               48         12
  15175307 王林                                     061101               50         13
  15181963 程明                                     061102               50         14
  15175307 王燕                                     061103               50         15
  15310758 韦严平                                   061104               50         16
  15056032 张强民                                   061109               50         17
  15250869 赵琳                                     061111               50         18
  15056032 张蔚                                     061110               50         19
  15113614 李方方                                   061106               50         20已选择11行。已用时间:  00: 00: 00.07-- 上面查询 RN 为13 的是“张蔚”，此时却查到的是“王林”，“ZXF”=50 的顺序都不太一样啦，也找不出是什么排序规律，
-- 所以求各位大虾：给个说法!

Answer 1

-- 测试脚本如下： CREATE TABLE XS
(
 XH char(6)  NOT NULL  CONSTRAINT PK_XS_XH PRIMARY KEY,
 XM varchar2(20) NOT NULL,
 ZYM char(20) NULL,
 XB char(4) NOT NULL CONSTRAINT CH_XS_XB CHECK(XB='男' or XB='女'),
 CSSJ  date  NOT NULL,
 ZXF number(2) NULL,
 BZ varchar2(100) NULL
 );INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061101','王林','计算机','男',TO_DATE('19860210','YYYYMMDD'),50);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061102','程明','计算机','男',TO_DATE('19870201','YYYYMMDD'),50);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061103','王燕','计算机','女',TO_DATE('19851006','YYYYMMDD'),50);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061104','韦严平','计算机','男',TO_DATE('19860826','YYYYMMDD'),50);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061106','李方方','计算机','男',TO_DATE('19861120','YYYYMMDD'),50);
INSERT INTO XS VALUES('061107','李明','计算机','男',TO_DATE('19861120','YYYYMMDD'),54,'提前修完《数据结构》，并获学分');
INSERT INTO XS VALUES('061108','林一帆','计算机','男',TO_DATE('19850805','YYYYMMDD'),52,'已提前修完一门课');
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061109','张强民','计算机','男',TO_DATE('19840811','YYYYMMDD'),50);
INSERT INTO XS VALUES('061110','张蔚','计算机','女',TO_DATE('19870722','YYYYMMDD'),50,'三好生');
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061111','赵琳','计算机','女',TO_DATE('19860318','YYYYMMDD'),50);
INSERT INTO XS VALUES('061113','严红','计算机','女',TO_DATE('19850811','YYYYMMDD'),48,'有一门功课不及格，待补考');
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061201','王敏','通信工程','男',TO_DATE('19840110','YYYYMMDD'),42);
INSERT INTO XS VALUES('061202','王林','通信工程','男',TO_DATE('19850129','YYYYMMDD'),40,'有一门功课不及格，待补考');
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061203','王玉民','通信工程','男',TO_DATE('19860326','YYYYMMDD'),42);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061204','马琳琳','通信工程','女',TO_DATE('19840210','YYYYMMDD'),42);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061206','李计','通信工程','女',TO_DATE('19850920','YYYYMMDD'),42);
INSERT INTO XS VALUES('061210','李红庆','通信工程','女',TO_DATE('19850501','YYYYMMDD'),44,'已提前修完一门课，并获得学分');INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061216','孙祥欣','通信工程','女',TO_DATE('19840309','YYYYMMDD'),42);INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061218','孙严','通信工程','男',TO_DATE('19861009','YYYYMMDD'),42);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061220','吴薇华','通信工程','女',TO_DATE('19860318','YYYYMMDD'),42);
INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES('061221','刘燕敏','通信工程','女',TO_DATE('19851112','YYYYMMDD'),42);
INSERT INTO XS VALUES('061241','罗林琳','通信工程','女',TO_DATE('19860130','YYYYMMDD'),44,'转专业学习');commit;

Answer 2

-- 也不是根据 rowid 或者 rownum 排序的：scott@SZTYORA> select rowid, t.* from xs t where zxf=50 order by rowid;ROWID              XH           XM                   ZYM                  XB       CSSJ               ZXF BZ
------------------ ------------ -------------------- -------------------- -------- -------------- ---------- ---------
AAAM0KAAEAAAA4WAAA 061101       王林                 计算机               男       10-2月 -86          50
AAAM0KAAEAAAA4WAAB 061102       程明                 计算机               男       01-2月 -87          50
AAAM0KAAEAAAA4WAAC 061103       王燕                 计算机               女       06-10月-85          50
AAAM0KAAEAAAA4WAAD 061104       韦严平               计算机               男       26-8月 -86          50
AAAM0KAAEAAAA4WAAE 061106       李方方               计算机               男       20-11月-86          50
AAAM0KAAEAAAA4WAAH 061109       张强民               计算机               男       11-8月 -84          50
AAAM0KAAEAAAA4WAAI 061110       张蔚                 计算机               女       22-7月 -87          50 三好生
AAAM0KAAEAAAA4WAAJ 061111       赵琳                 计算机               女       18-3月 -86          50已选择8行。已用时间:  00: 00: 00.09
scott@SZTYORA> select rowid, t.* from xs t where zxf=50 order by rownum;ROWID              XH           XM                   ZYM                  XB       CSSJ               ZXF BZ
------------------ ------------ -------------------- -------------------- -------- -------------- ---------- ---------
AAAM0KAAEAAAA4WAAA 061101       王林                 计算机               男       10-2月 -86          50
AAAM0KAAEAAAA4WAAB 061102       程明                 计算机               男       01-2月 -87          50
AAAM0KAAEAAAA4WAAC 061103       王燕                 计算机               女       06-10月-85          50
AAAM0KAAEAAAA4WAAD 061104       韦严平               计算机               男       26-8月 -86          50
AAAM0KAAEAAAA4WAAE 061106       李方方               计算机               男       20-11月-86          50
AAAM0KAAEAAAA4WAAH 061109       张强民               计算机               男       11-8月 -84          50
AAAM0KAAEAAAA4WAAI 061110       张蔚                 计算机               女       22-7月 -87          50 三好生
AAAM0KAAEAAAA4WAAJ 061111       赵琳                 计算机               女       18-3月 -86          50已选择8行。

Answer 3

环境的:
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as scott

Answer 4

-- ZXF=50 的 其他的 RN 都对吗？

Answer 5

-- 我用的是 Oracle 10g (10.2.0.4)

Answer 6

我用rownum都会遇到这种情况

Answer 7

我有一次也用这种函数,只不过order的字段不同
取出来的记录数竟然不一样多的
一直也没找出原因来

Answer 8

呵呵,之前有帖子说过,9I 的GROUP BY 会自动排序.
10G以后不会,
刚想了想,
你最后两句
select rowid, t.* from xs t where zxf=50 order by rowid;
得到的结果是正确的顺便,那么
select rowid, t.* from xs t where zxf=50 order by rownum;t.rn>=10 and t.rn<=20,触发了按照ROWID排序,猜想是如此的你测试下,select xh, xm,xh,zxf,row_number() over(order by zxf) rn from xs order by rowid;试试

Answer 9

-- 嗯，在 row_number() 函数中，都加个 rowid 就 三个查询都 一致啦：
--------- Oracle 奇怪的 row_number() 函数，大家来分析一下..........-- 查询一：直接查询(无子查询时)scott@SZTYORA> select xh, xm,xh,zxf,row_number() over(order by zxf,rowid) rn from xs;
XH           XM                                       XH                  ZXF         RN
------------ ---------------------------------------- ------------ ---------- ----------
061202       王林                                     061202               40          1
061201       王敏                                     061201               42          2
061203       王玉民                                   061203               42          3
061204       马琳琳                                   061204               42          4
061206       李计                                     061206               42          5
061216       孙祥欣                                   061216               42          6
061218       孙严                                     061218               42          7
061220       吴薇华                                   061220               42          8
061221       刘燕敏                                   061221               42          9
061210       李红庆                                   061210               44         10
061241       罗林琳                                   061241               44         11
061113       严红                                     061113               48         12
061101       王林                                     061101               50         13
061102       程明                                     061102               50         14
061103       王燕                                     061103               50         15
061104       韦严平                                   061104               50         16
061106       李方方                                   061106               50         17
061109       张强民                                   061109               50         18
061110       张蔚                                     061110               50         19
061111       赵琳                                     061111               50         20
061108       林一帆                                   061108               52         21
061107       李明                                     061107               54         22已选择22行。已用时间:  00: 00: 00.10--------------------------------------------------------------------------------------------- 查询二：有子查询(但外层查询无条件时，此时还跟查询一结果一致)scott@SZTYORA>  select * from (
  2    select ascii(xm) as asc_xm, xm,xh,zxf,row_number() over(order by zxf,rowid) rn from xs ) t;    ASC_XM XM                                       XH                  ZXF         RN
---------- ---------------------------------------- ------------ ---------- ----------
  15175307 王林                                     061202               40          1
  15175307 王敏                                     061201               42          2
  15175307 王玉民                                   061203               42          3
  15313324 马琳琳                                   061204               42          4
  15113614 李计                                     061206               42          5
  15052185 孙祥欣                                   061216               42          6
  15052185 孙严                                     061218               42          7
  15044788 吴薇华                                   061220               42          8
  15042712 刘燕敏                                   061221               42          9
  15113614 李红庆                                   061210               44         10
  15187351 罗林琳                                   061241               44         11
  14989477 严红                                     061113               48         12
  15175307 王林                                     061101               50         13
  15181963 程明                                     061102               50         14
  15175307 王燕                                     061103               50         15
  15310758 韦严平                                   061104               50         16
  15113614 李方方                                   061106               50         17
  15056032 张强民                                   061109               50         18
  15056032 张蔚                                     061110               50         19
  15250869 赵琳                                     061111               50         20
  15113879 林一帆                                   061108               52         21
  15113614 李明                                     061107               54         22已选择22行。已用时间:  00: 00: 00.06--------------------------------------------------------------------------------------------- 查询三：有子查询(但外层查询有条件时，此时查询结果 RN 发生了变化，为什么呢？)
scott@SZTYORA>  select * from (
  2    select ascii(xm) as asc_xm, xm,xh,zxf,row_number() over(order by zxf,rowid) rn from xs ) t
  3    where t.rn>=10 and t.rn<=23;    ASC_XM XM                                       XH                  ZXF         RN
---------- ---------------------------------------- ------------ ---------- ----------
  15113614 李红庆                                   061210               44         10
  15187351 罗林琳                                   061241               44         11
  14989477 严红                                     061113               48         12
  15175307 王林                                     061101               50         13
  15181963 程明                                     061102               50         14
  15175307 王燕                                     061103               50         15
  15310758 韦严平                                   061104               50         16
  15113614 李方方                                   061106               50         17
  15056032 张强民                                   061109               50         18
  15056032 张蔚                                     061110               50         19
  15250869 赵琳                                     061111               50         20
  15113879 林一帆                                   061108               52         21
  15113614 李明                                     061107               54         22已选择13行。已用时间:  00: 00: 00.01

Answer 10

我测试的结果没有问题，oracle版本是10.2.0.11 49390 李红庆 61210 44 10
2 49886 罗林琳 61241 44 11
3 53711 严红  61113 48 12
4 54725 张蔚 61110 50 13
5 54725 张强民 61109 50 14
6 49390 李方方 61106 50 15
7 52725 王林 61101 50 16
8 54740 赵琳 61111 50 17
9 52900 韦严平 61104 50 18
10 52725 王燕 61103 50 19
11 46028 程明 61102 50 20

Answer 11

是正确的 ，order by zxf,rowid 相同的zxf 按插入时的物理地址排序是正确的   顶 

Answer 12

楼主，我给你发现了这个问题——————--只要 t.rn 是属于小于的不等式才出现，，你把t.rn<20删除掉就能正常显示。
select *
  from (select ascii(xm) as asc_xm,
               xm,
               xh,
               zxf,
               row_number() over( order by zxf) rn
          from xs) t
 where t.rn >=13
 
minusselect *
  from (select ascii(xm) as asc_xm,
               xm,
               xh,
               zxf,
               row_number() over( order by zxf) rn
          from xs) t
 where t.rn >=21
--这个在大于不等式下应该是稳定的，但是如果是小于不等式的话就好像是按另一个排序了，这个问题真得问ORACLE啊
怪哉

Answer 13

我觉得ORACLE在 分析函数中 排序的实现中 ‘大于’和‘小于’的条件是按两种方式走的——而且按‘小于’号走的路线有问题，不稳定select *
  from (select ascii(xm) as asc_xm,
               xm,
               xh,
               zxf,
               row_number() over( order by zxf) rn
          from xs) t
 where t.rn <24 minus select *
  from (select ascii(xm) as asc_xm,
               xm,
               xh,
               zxf,
               row_number() over( order by zxf) rn
          from xs) t
 where t.rn <13
 -- and 
 --t.rn <= 100;--如果你运行以上的语句，将一团糟糕，意料之外

Answer 14

“张蔚 和“王林的zxf值相同，无先后

Answer 15

Row_Number() over(Pratition by <字段名> order by <字段名> desc or asc) 
Rank() 排序不会显示连续的编号 
Dense_Rank()会连续显示编号
Ntile(n) 根据制定的分组将结果集分区（partition） 并记录其在组中所在的位置。pivot(<聚合函数> for pivot 字段 in(字段列表))其实这几个函数你可以了解一下，

Answer 16

因为你的排序order by zxf是不对的，zxf有重复值无法保证排序，必须加rowid
order by zxf,rowid或者找个能够保证唯一的列

Answer 17

正常的，结果没错就对了。本身就是按窗口子句来order,里面姓名有先后是正常的。

Answer 18

分析函数是在SQL语句最后执行的！！！所以没有错

Answer 19

两者的执行计划不一样
SQL> set autot trace explain
SQL> select xh,xm,xh,zxf,row_number()over(order by zxf) rn from xs;执行计划
----------------------------------------------------------                      
Plan hash value: 219165360                                                      
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |    22 |   726 |     4  (25)| 00:00:01 |     
|   1 |  WINDOW SORT       |      |    22 |   726 |     4  (25)| 00:00:01 |     
|   2 |   TABLE ACCESS FULL| XS   |    22 |   726 |     3   (0)| 00:00:01 |     
---------------------------------------------------------------------------    
                                         
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement                                   SQL> select * from (select xh,xm,zxf,row_number()over(order by zxf) rn from xs) where rn>=10 and rn<=20执行计划
----------------------------------------------------------  
Plan hash value: 2407451311                                                     
                                                                                
---------------------------------------------------------------------------------  
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT         |      |    22 |  1012 |     4  (25)| 00:00:01 |  
|*  1 |  VIEW                    |      |    22 |  1012 |     4  (25)| 00:00:01 |   
|*  2 |   WINDOW SORT PUSHED RANK|      |    22 |   726 |     4  (25)| 00:00:01 |  
|   3 |    TABLE ACCESS FULL     | XS   |    22 |   726 |     3   (0)| 00:00:01 |      
---------------------------------------------------------------------------------  Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - filter("RN">=10 AND "RN"<=20)                                            
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "ZXF")<=20)                                       
                                                                                
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement  
一个是WINDOW SORT，一个是WINDOW SORT PUSHED RANK
具体区别我也说不清楚
这个排序的不确定性在分页时会造成困扰
可以加上rowid来解决

Answer 20

像这种列值不唯一的排序一般都在order by后加rowid或主键

Answer 21

这个结果很正常啊，over()是窗口函数，over后面跟随的order by 和一般的order by不一样，如果是正常的order by 的话，数据会读到内存中，这个时候写子查询按照rownum来排序，就会跟order by 的顺序一样，可以实现分页功能，但是窗口函数不具备将所有表数据读到内存中，然后按窗口排序功能，所以这个时候你写子查询按照rownum排序，自然是无序的，就跟 select * from table where rownum<10一样，结果是无序的。

Answer 22

我的测试版本是9.2.0.1.0  没有发现楼主的问题。我这没有10g的测试环境，楼主可以试着修改一下用9i的编译器执行上面的sql（具体命令记不起来了），测试是否还出现问题。

Answer 23

order by后面的列必须能保证稳定排序,那么结果才是可预计的,否则在不同的写法下,可能的执行计划差异,都可能导致不一样的排序结果

Answer 24

row_number ,rank 等排名函数的区别啊，row_number，over中 order 不是唯一排序的话，排名序号是随机的吧...

Answer 25

看来对相同内容的数据排序要注意了，呵呵，不多再加个rowid可以解决。呵呵。

Answer 26

很简单呀
问题就出在你有相同的数据ZXF=50的那些
而你只有一个order by zxf 
一样的值 你让ORACLE怎么给你排 呵呵

Answer 27

加rowid可以成功那是因为rowid是唯一的 就不会出现重复值问题
你加个其他字段在后面 只要唯一 也可以保证每次查询出的结果正确

Answer 28

没有必要再按rowi排序，如果哪次发生了movment，那么这个rowid排序也没意义了。