在Oracle SQL/分页中选择结果的范围(限制) [英] Select range (limit) of results in Oracle SQL / pagination

问题描述

假设我们有下表:

CREATE TABLE "ARTICLE_COUNTER_STATISTICS_M"
(
  "ID"        NUMBER(19,0) NOT NULL ENABLE,
  "ITEMCOUNT" NUMBER(19,0),
  "VERSION" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
)

唯一约束位于ID和VERSION字段上.

Unique constraint is on ID and VERSION field.

可能在数据库中的某些行的示例(所有记录的时间戳始终相同):

Example of some rows that could be in DB(Timestamps is be the same for all records always):

1374659422641   22  2014.02.26 09:45:01,000000000
1387797258001   7   2014.02.26 09:45:01,000000000
1387796687862   1   2014.02.26 09:45:01,000000000
1387800521317   1   2014.02.26 09:45:01,000000000

现在，如果我们要选择ID，itemcount并按itemcount对其进行排序，我们将执行以下操作:

Now, if we want to select the IDs, itemcount and order them by itemcount we would be doing something like this:

SELECT id, SUM(itemcount) as count, version 
FROM ARTICLE_COUNTER_STATISTICS_m 
WHERE id != '0' 
GROUP BY id, version 
ORDER BY version DESC, SUM(itemcount) DESC 

但是尚不清楚的是，我们如何选择结果仅在一定范围内.例如10到20个计数最高的商品?我尝试过这样的事情:

But the thing that is unclear, how do we select results only in certain range. For example 10 to 20 items with most count? I tried something like this:

SELECT id, count, version FROM( 
    SELECT id, SUM(itemcount) as count, version 
    FROM ARTICLE_COUNTER_STATISTICS_m 
    WHERE id != '0' 
    GROUP BY id, version 
    ORDER BY version DESC, SUM(itemcount) DESC 
) where rownum >= 0 and rownum <= 20

但是，如果"where rownum> = n"中的n大于1，则它不起作用(它只会返回空结果).我知道可能必须使用ROW_NUMBER()函数来实现该目标，但是我无法使其正常工作.有任何想法吗?谢谢！

But it does not work with if n in "where rownum >= n" is greater than 1 (it just returns empty results). I know that probably ROW_NUMBER() function has to be used to accomplish the goal, but I could not get it working. Any ideas? Thx!

推荐答案

下面是一个row_number示例:

SELECT * 
FROM   (SELECT id, itemcount, version, 
               ROW_NUMBER() OVER (ORDER BY version DESC, itemcount DESC) AS rn
        FROM   (SELECT id, SUM(itemcount) as itemcount, version 
                FROM ARTICLE_COUNTER_STATISTICS_m 
                WHERE id != '0' 
                GROUP BY id, version
               )
        )
WHERE  rn BETWEEN 3 AND 20 -- just an example

请注意，row_number为具有相同属性的项目分配任意顺序(根据window子句).因此，例如，如果您的前三个项目都具有version=100和itemcount=100，而您使用的是rn BETWEEN 2 AND 4，则无法确定哪个将被排除.为避免这种情况，可以使用rank，它为相同属性的项目(同样，由window子句定义)分配相同的值.

Note that row_number assigns arbitrary order to items with the same properties (as per the window clause). So, if for example, your first three items all have version=100 and itemcount=100 and you use rn BETWEEN 2 AND 4, there's no telling which one will be excluded. To avoid this, you can use rank, which assigns the same value to items of the same properties (again, as defined by the window clause).

这篇关于在Oracle SQL/分页中选择结果的范围(限制)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！