高手们来看个奇葩问题

Question

如下：表tt 有字段id name statecreate table TT(id NUMBER not null,name VARCHAR2(20),state CHAR(1))alter table TTadd constraint SSS primary key (ID)记录如下：id name state1 cj 02 cj 03 dd 04 asd4 05 asd3 06 asd2 17 asd1 18 as 19 ssa 010 sd 011 a 012 gdfg 013 xvc 014 xv 015 v 016 c 017 xcv 018 xv 019 xv 020 x 0现在我利用rownumSELECT * FROM (SELECT * FROM tt ORDER BY state) WHERE ROWNUM <=9;SELECT * FROM (SELECT * FROM tt ORDER BY state) WHERE ROWNUM <=30为什么前9条的不一样呢？是由于什么原因？

Answer 1

还有个问题，
如果如上的数据，state为0的有17条
SELECT * FROM (SELECT * FROM tt ORDER BY state) WHERE ROWNUM <=M;SELECT * FROM (SELECT * FROM tt ORDER BY state) WHERE ROWNUM <=30那么当m>=17时， 两个列表是一样的。

Answer 2

SELECT * FROM (SELECT * FROM tt ORDER BY state) WHERE ROWNUM <=9;SELECT * FROM (SELECT * FROM tt ORDER BY state) WHERE ROWNUM <=30-- 你的意思是“小于等于30的结果集中的前9条记录”与“小于等于9的结果集”排列顺序不一样，
-- 是这个意思么？-- 有必要这么较真么？Oracle中，不同的where条件值，走的执行计划不一定一样，你的要求只是state字段按顺序排列，那么剩下的两个字段，oracle就可以随便排列了，既然能随便排列，哪能要求它们的前N条记录结果集一样呢？

Answer 3

顶2楼，楼主你按state排序的，0的记录就17条， 两个列表是肯定一样了

Answer 4

这样会发生的问题是：
如果现在进行分页，pagesize是9  那么1到9的记录
并不是1到20的子集。结果就是如果有18条记录 现在pagesize为9 分两页。那么rownum >=1 && rownum<=9
和 rownum>=10&&rownum<=18的结果集 不是rownum>=1&&rownum <= 18的子集这样会导致记录的丢失。可能有一条记录，不会包含在上面两页的任何一页中。求教原理。不要说“我以为。”

Answer 5

1 cj1 0
2 cj2 0
3 cj3 0
4 cj4 0
5 cj5 0
6 cj6 1
7 cj7 1
8 cj8 1
9 cj9 0
10 cj10 0
11 cj11 0
12 cj12 0
13 cj13 0
14 cj14 0
15 cj15 0
16 cj16 0
17 cj17 0
18 cj18 0
19 cj19 0
20 cj20 0如图所示的记录
分别执行sql如下：
第一页SELECT * FROM (SELECT temp.*, ROWNUM rn  FROM (SELECT * FROM tt ORDER BY state) temp WHERE ROWNUM <=10) WHERE rn >=1
1 cj1 0 1
2 cj2 0 2
3 cj3 0 3
4 cj4 0 4
5 cj5 0 5
10 cj10 0 6
13 cj13 0 7
12 cj12 0 8
11 cj11 0 9
9 cj9 0 10
第二页
SELECT * FROM (SELECT temp.*, ROWNUM rn  FROM (SELECT * FROM tt ORDER BY state) temp WHERE ROWNUM <=20) WHERE rn >=11;
19 cj19 0 11
18 cj18 0 12
17 cj17 0 13
15 cj15 0 14
13 cj13 0 15
11 cj11 0 16
9 cj9 0 17
6 cj6 1 18
7 cj7 1 19
8 cj8 1 20发现了没有？id为16的在两页里都没有出现
我只想知道原因。

Answer 6

原因是，你的两个子查询的where 条件不一样，导致两个查询的执行计划不一样，所以结果也不一样!第一个子查询： WHERE ROWNUM <=10第二个子查询： WHERE ROWNUM <=20-- 当你表中总共有30条记录时，如果state字段有索引的话，也许第一个查询会走索引扫描，而第二个查询也许会走全表扫描(例如，Oracle会估算你将要返回的记录与表的总记录的比率，当大于50%，Oracle会认为 ：你的SQL语句可能走全表扫描更有效)

Answer 7

最好的解决方法是用row_number()分析函数来解决分页问题。

Answer 8

oracle de rownum 是在提取记录就已经生成了，它限于排序操作，所以必须使用子查询先排序
参考：http://wenku.baidu.com/view/f8a0c5c708a1284ac8504372.html

Answer 9

试试这个
SELECT * 
  FROM (SELECT temp.*, 
               ROWNUM rn 
          FROM (SELECT * 
                  FROM tt 
                 ORDER BY state) temp) 
 WHERE rn >=1
   AND rn <=10
和
SELECT * 
  FROM (SELECT temp.*, 
               ROWNUM rn 
          FROM (SELECT * 
                  FROM tt 
                 ORDER BY state) temp) 
 WHERE rn >=11
   AND rn <=20看看结果是你要的吗

Answer 10

顶楼上,分页的话强烈建议使用RN!!!!!!显示的时候,把RN这一列删除了就是~~

Answer 11

这就相当于索引,把每条记录编个号,这样就方便不同的order by.

Answer 12

呵呵，本质的不同是不要把rownum直接放在查询条件里，因为这个rownum是在order by之前生成的。不信你可以把rownum写在查询的列中，看看是什么结果，比如select rownum, column1, column2 
  from table1
 order by column1rownum列的查询结果在多数情况下应该不是连续的1，2，3...所以如果你想用rownum表示order by的顺序，则需要3部
1. 用order by 生成一个结果
2. 在第一部的结果中加上rownum，为第1步的结果编上号，并给这个rownum起一个别名，比如rn,像你做的那样
3. 基于第2步的结果，把rn作为条件来进行你的查询。by the way, 作为一个oracle开发人员，在你用淘宝语”亲“称呼别人时，你心中应该比别人想到更多的一点，那就是：oracle的顶尖高手被称为黑桃尖，全球的黑桃尖大概有150多人，中国有20多人，其中2位在淘宝。
所以，加油吧，兄弟们;-)

Answer 13

排序的时候可以考虑 ORDER BY state,id 
这样应该就能避免那种情况了。
至于为什么出现你说的这种问题，楼上好多人都解释了，那个排序不是绝对的。就好比按照id排序的时候，如果两个id一样，那哪个排前，排后是不定的吧。