oracle里的限制返回行数量

一时失言乱红尘 2021-10-23 23:40 325阅读 0赞

这个问题，SQL SERVER是很方便的，使用top就行了。mysql也不赖，limit。但号称龙头老大的oracle呢？不存在的。要曲线救国。

使用rownum。这是一个伪列。使用的时候，如果有排序的话，不能直接就用，否则出来的效果很奇怪，要嵌套使用。

比如说，我想找考试成绩排名前5的记录，不能这样写：

select * from exam where rownum<=5 order by score desc

要这样写：

select * from （select * from exam order by score desc） where rownum<=5

按道理，这种文章很早之前就应该记录下来的，但不知为什么，一直没去做。其结果就是，每次都要谷歌或百度，看一堆详解、细说、大全，不知所谓。

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

2019.12.28  
这种使用rownum的语句，要注意rownum是系统保留字，在一条SQL语句中，每一层查询都会有相应的rownum。因此，如果要分页的话，应该使用rownum的别名。例如：

select * from (
         select rownum ,p.* from project p where id>0 and rownum<=20
         order by id
    ) t1
    where t1.rownum>=10

这个查询返回的记录数为0！为什么呢？因为第一个rownum（括号里的）是子查询的，而外层的rownum是最外层，它们不是同一个东西。所以应该改为

select * from (
         select rownum as rowno ,p.* from project p where id>0 and rownum<=20
         order by id
    ) t1
    where t1.rowno>=10

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

2020.1.3  
这个rownum的别扭远超我的想象！

Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。  
SQL> select rownum ,id,name from student order by name;  
ROWNUM ID NAME

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

3 200003 李三
         2 200002 王二
         1 200001 张一
         4 200004 赵四

可以看出，**rownum并不是按照name列来生成的序号**。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，**必须使用子查询**；  
SQL> select rownum ,id,name from (select \* from student order by name);  
ROWNUM ID NAME

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

1 200003 李三
         2 200002 王二
         3 200001 张一
         4 200004 赵四

这样就成了按name排序，并且用rownum标出正确序号（有小到大）

也就是说，`select rownum ,id,name from student order by name;`语句中，你以为rownum是按照order b  
y name 的顺序来排列的？那你实在是太幼稚了！那它究竟是怎么产生的？不清楚。如果是按照什么物理记录当初插入的顺序产生，那谁能告诉我使用了子查询忽然又好了。如前所述，可能是每一层查询都会有相应的rownum，这个rownum是依赖于它底下一层的。所以，在子查询中，rownum根据物理记录插入的顺序产生；而在外层的rownum，就根据子查询的排序产生了。

嗯，必定是这样的。