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

问题描述

当我使用 rownum 和 between 关键字时，查询不会返回任何行.任何人都可以解释查询未检索任何行的原因吗?

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.

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