在使用between关键字时，行编号不显示任何行 [英] row num is not displaying any rows when using between keyword

问题描述

当我在关键字之间使用 rownum 和时，查询不会返回任何行.谁能解释查询不检索任何行的原因?

When I am using rownum and between keywords then the query doesn't return any rows. Can anyone explain the reason why query is not retrieving any rows?

select * from cus where rownum between 2 and 6;

我只想检查将rownum与between关键字一起使用时是否可以工作.因此，我只是尝试了上面的查询以显示2到6之间的行.但是当我尝试执行查询时，它不检索任何行.

I just want to check whether rownum will work when it is used with between keyword .So ,I just tried the above query to display the rows which are in between 2 and 6. But when I tried to execute the query, it doesn't retrieve any rows.

预先感谢

推荐答案

Oracle rownum从1开始，因此如果您说between 2 and N，您将永远不会得到第一个rownum.

Oracle rownum starts at 1, so you will never get the first rownum if you say between 2 and N.

需要一行来初始化" rownum伪列序列，因此，通过在您的条件中消除rownum 1，可以消除所有rownum(或每行本质上具有rownum 0).

It takes a row to "initiate" the rownum pseudocolumn sequence, so by eliminating rownum 1 in your criteria, you eliminate all rownums (or every row essentially has rownum 0).

这样看.在数据库向您返回一行之前，您不会得到ROWNUM.任何条件的第一行始终为ROWNUM 1.

Look at it like this. You don't get a ROWNUM until the database returns a row to you. The first row of any criteria will always be ROWNUM 1.

现在，您可以使用的技巧是使用子查询.每个子查询将具有其自己的rownum，如果将其别名为另一个列名，则可以将其保留在外部查询中，并根据需要对其进行处理.因此，如果要实现对结果集的分页，通常会将rownum从内部结果作为rownum_别名到外部子查询，以使用BETWEEN进行限制.

Now, the trick you can use is to use a subquery. Each subquery will have its own rownum, and if you alias it to another column name, you can preserve it into outer queries, and treat it however you like. So if you are looking to implement paging of a result set, you would normally alias rownum from inner results as rownum_ to an outer subquery to limit with BETWEEN.

select * from 
  (select t.*, rownum as rownum_ from t)
where rownum_ between 2 and 6

但是请注意，外部结果集将具有其自己的rownum，因此您可以这样做:

But note, that the outer result set will have its own rownum, so you could do:

select t2.*, rownum from 
  (select a, b, rownum as rownum_ from t) t2
where rownum_ between 2 and 6

您将看到最终结果上的rownum仍从1开始，但是内部结果中的rownum_从2开始.

You will see rownum on the final result still starts at 1, but your inner result will have rownum_ starting at 2.

这篇关于在使用between关键字时，行编号不显示任何行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！