sql(oracle)选择前10条记录，然后选择下10条，依此类推 [英] sql (oracle) to select the first 10 records, then the next 10, and so on

问题描述

我认为我可能只是缺少一些明显的语法，但是选择前10条记录，然后选择下10条记录，等等的sql(oracle)是什么?

I figure I might just be missing some obvious syntax but what is the sql (oracle) to select the first 10 records, then the next 10, and so on?

我尝试使用rownum，但似乎可以得到rownum> X和rownum<是的.

I tried using rownum but can seem to get rownum > X and rownum < Y to work.

llcf

推荐答案

只有一种相当复杂的方法可以做到这一点，这对于Oracle来说确实是一个痛苦.他们应该只实现LIMIT/OFFSET子句...

There is only a rather convoluted way to do this, which is a real pain with Oracle. They should just implement a LIMIT/OFFSET clause...

行编号在where子句选择之后被赋予 ，因此行编号必须始终以1开头.where rownum > x始终为false.

The rownum gets assigned after the row has been selected by the where clause, so that a rownum must always start with 1. where rownum > x will always evaluate to false.

此外，在排序完成之前，已为rownum分配了 ，因此rownum的顺序与您说的顺序不同.

Also, rownum gets assigned before sorting is done, so the rownum will not be in the same order as your order by says.

您可以通过子选择解决两个问题:

You can get around both problems with a subselect:

 select a,b,c, rn from 
    ( select a,b,c, rownum rn from 
         ( select a,b,c from the_table where x = ? order by c)
      where rownum < Y)
  where rn > X

如果您不需要排序(仅那时)，则可以简化为

If you do not need to sort (but only then), you can simplify to

 select a,b,c, rn from
     ( select a,b,c, rownum rn from the_table where rownum < Y )
   where rn > X

这篇关于sql(oracle)选择前10条记录，然后选择下10条，依此类推的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！