关于Oracle数据库的rownum应用

  它是Oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,以此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。

  如以下语句将无法正常运行:

select student.*, student.rownum from student;

  我们如果要将rownum这个伪字段动态产生的列显示出来,需要使用如下语句:

select t.*, rownum from student t;

  如果我们在查询时加入了限制条件,则rownum又将动态生成。结论就是rownum是不会与任何一行进行绑定都是根据查询后的记录来生成的:

select t.*, rownum from student t where sage > 25;

  由于rownum的特殊性,我们在使用rownum时必须注意以下事项:

  • 如果希望找到学生表中的第一条学生信息,可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。因为rownum始终从1开始,因此1以上的自然数在rownum做等于判断时都是false条件,所以无法查到rownum=n(n>1的自然数)。
select * from student where rownum = 1;  -- 能查询到第一条记录
select * from student where rownum = 2;  -- 不能查询第二条记录
  • 如果想找到从第二行记录以后的记录,当时用rownum>2是查不出来记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle认为rownum>n(n>1是自然数)这种条件依旧不成立,所以查不到记录。
select * from student where rownum > 2;  -- 不能查询出记录
  • 如果想找到第三条记录之前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n(n>1的自然数)的条件认为是成立的,所以可以找到记录。
select * from student where rownum < 3;  -- 选择前两条记录
  • 如果要查询后三条记录,则要考虑的情况又会比较复杂一些,比如我们按学号(SID)和年龄(SAGE)进行倒序排列,查询最后三条记录,检查下面两个sql语句的结果有何异同:
--  正常返回最后三条记录
select * from student where rownum < 4 order by sid desc;
--  返回了前三条记录
select * from student where rownum < 4 order by sage desc;

  我们可以看到,同样的sql语句,只是排列序不一样,就导致了完全不一样的结果。两个语句的对比中可以看出在第二条语句中rownum并不是以SAGE列生成序列号,而是在插入记录时以主键列为参考生成的。之所以我们在使用第一条语句时可以成功取得最后三条,原因就在于第一条语句是按照主键进行排序的。

  • 如果要按照非主键为依据来进行最后记录数的提取,我们需要使用复合查询语句。
select * from (select * from student order by sage desc) where rownum < 4;
  • 有的时候我们需要对记录进行分页,这时我们会期望返回一个指定范围内的记录,如,返回第10至第20条,可使用如下sql语句:
select * from student where rownum < 20
minus    --  将两个记录集相减
select * from student where rownum < 10;

  或者

select * from (select rownum r, student.* from student where rownum < 20 order by sid) where r >= 10;
  • 提到rownum,不得不得到Oracle表中另外一个隐藏列:rowid,这是存在于每一个表中的用于唯一标识一行数据的由Oracle自动生成和管理的属性,我们可以认为这是Oracle数据表中的超键,在同一个数据库中,这个超键永远都不会有重复。rowid主要用于快速定位某一行数据,有点类似于一本书的页码,可以快速找到内容,常用于索引中。下面这条sql语句可以帮助我们查看每一行数据的rowid。
select student.*, rowid from student;

 

上一篇:Java中oracle分页查询01


下一篇:2019年10月21日 数据库sql只取最新一条的数据