假设我们有下表:
CREATE TABLE "ARTICLE_COUNTER_STATISTICS_M"
(
"ID" NUMBER(19,0) NOT NULL ENABLE,
"ITEMCOUNT" NUMBER(19,0),
"VERSION" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
)
唯一约束是ID和VERSION字段.
可能在DB中的某些行的示例(时间戳对于所有记录始终相同):
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对它们进行排序,我们将执行以下操作:
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项最重要的项目?我试过这样的事情:
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
但是,如果“rownum> = n”中的n大于1(它只返回空结果),则无效.我知道可能必须使用ROW_NUMBER()函数来实现目标,但我无法让它工作.有任何想法吗?谢谢!