oracle中rownum的奥秘 [英] Mystery of rownum in oracle

问题描述

我正在努力根据 rownum 获取数据.当我执行以下查询以获取基于 rownum 1 到 4 的结果时，它工作正常.

I am struggling to fetch the data based on rownum. When I execute the below query to get the results based rownum 1 to 4 then it is working fine.

  SELECT ROWNUM TOTAL,MI.* FROM (SELECT USER_ID,CUSTOMER_NAME FROM ELEC_AUTO_MERC 
  ORDER BY CREATION_DATE DESC ) MI WHERE ROWNUM BETWEEN 1 AND 4;

但是当我执行相同的查询以从 rownum 2 to 4 获取结果时，它不起作用，它不返回任何内容.

But when I am executing same query to get result from rownum 2 to 4 then it is not working, it doesn't return anything.

  SELECT ROWNUM TOTAL,MI.* FROM (SELECT USER_ID,CUSTOMER_NAME FROM ELEC_AUTO_MERC 
  ORDER BY CREATION_DATE DESC ) MI WHERE ROWNUM BETWEEN 2 AND 4;

作为一种解决方法，当我再使用一个 SELECT 语句时它工作正常，但我认为仅对 rownum 多次使用 SELECT 不是好方法.

As a workaround, when I use one more SELECT statement then it is working fine, but I don't think it is good approach to use SELECT multiple times only for rownum.

SELECT * FROM (SELECT ROWNUM TOTAL,MI.* FROM (SELECT USER_ID,CUSTOMER_NAME FROM ELEC_AUTO_MERC 
  ORDER BY CREATION_DATE DESC ) MI) WHERE TOTAL BETWEEN 2 AND 4;

你能帮我创建优化查询吗?

Can you please help me out to create optimize query?

推荐答案

ROWNUM 很奇怪，因为它可以作为查询条件的一部分进行评估 - 但如果该行未能通过该过滤器，分配给它的 ROWNUM 值可以再次用于下一行.

ROWNUM is weird in that it can be evaluated as part of a condition in the query - but if the row then fails to pass that filter, the ROWNUM value that it was assigned becomes available to be used again for the next row.

这样做的一个重要影响是，如果您使用任何排除 ROWNUM 值为 1 的条件，您将永远不会得到匹配项.针对此条件进行测试的第一行将是第 1 行；但随后它将无法通过测试，因此下一行将被视为第 1 行；等等.

One important effect of this is that if you use any condition that excludes a ROWNUM value of 1, you will never get a match. The first row to be tested against this condition will be row 1; but then it will fail the test, so the next row will then be considered row 1; and so on.

所以你的条件 ROWNUM BETWEEN 2 AND 4 永远不可能为真.

So your condition ROWNUM BETWEEN 2 AND 4 can never be true.

您找到的解决方法是传统方法.另一种方法是使用分析函数对行进行排名，然后对排名进行过滤，例如:

The workaround you have found is the traditional one. Another would be to use an analytic function to rank the rows, then filter on the rank, e.g.:

SELECT MI.* FROM (
  SELECT USER_ID,CUSTOMER_NAME, RANK() OVER (ORDER BY CREATION_DATE DESC) AS the_rank
  FROM ELEC_AUTO_MERC 
  ) MI
WHERE the_rank BETWEEN 2 AND 4;

几个分析函数 - RANK、DENSE_RANK 和 ROW_NUMBER - 可用于此目的，并且会产生略有不同的结果，尤其是在有平局的情况下.查看文档.

Several analytic functions - RANK, DENSE_RANK, and ROW_NUMBER - can be used for this purpose, and will produce slightly different results, especially if there are ties. Check out the docs.

这篇关于oracle中rownum的奥秘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！