SQL Oracle SELECT TOP 10 记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2498035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SELECT TOP 10 records
提问by opHASnoNAME
I have an big problem with an SQL Statement in Oracle. I want to select the TOP 10 Records ordered by STORAGE_DB which aren't in a list from an other select statement.
我在 Oracle 中的 SQL 语句有一个大问题。我想选择 STORAGE_DB 排序的前 10 条记录，这些记录不在其他选择语句的列表中。
This one works fine for all records:
这个适用于所有记录：
SELECT DISTINCT
APP_ID,
NAME,
STORAGE_GB,
HISTORY_CREATED,
TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE
FROM HISTORY WHERE
STORAGE_GB IS NOT NULL AND
APP_ID NOT IN (SELECT APP_ID
FROM HISTORY
WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')
But when I am adding
但是当我添加
AND ROWNUM <= 10
ORDER BY STORAGE_GB DESC
I'm getting some kind of "random" Records. I think because the limit takes in place before the order.
我得到了某种“随机”记录。我认为是因为限制发生在订单之前。
Does someone has an good solution? The other problem: This query is realy slow (10k+ records)
有人有好的解决方案吗？另一个问题：这个查询真的很慢（10k+ 条记录）
回答by Padmarag
You'll need to put your current query in subquery as below :
您需要将当前查询放在子查询中，如下所示：
SELECT * FROM (
SELECT DISTINCT
APP_ID,
NAME,
STORAGE_GB,
HISTORY_CREATED,
TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE
FROM HISTORY WHERE
STORAGE_GB IS NOT NULL AND
APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
ORDER BY STORAGE_GB DESC )
WHERE ROWNUM <= 10
Oracle applies rownumto the result after it has been returned.
You need to filter the result after it has been returned, so a subquery is required. You can also use RANK()function to get Top-N results.
For performance try using NOT EXISTSin place of NOT IN. See thisfor more.
Oracle在返回结果后将rownum应用于结果。
您需要在返回结果后对其进行过滤，因此需要一个子查询。您还可以使用RANK()函数来获得 Top-N 结果。
为了提高性能，请尝试使用NOT EXISTS代替NOT IN. 请参阅此了解更多信息。
回答by Volpato
If you are using Oracle 12c, use:
如果您使用的是 Oracle 12c，请使用：
FETCH NEXT NROWS ONLY
FETCH NEXT ñONLY ROWS
SELECT DISTINCT
APP_ID,
NAME,
STORAGE_GB,
HISTORY_CREATED,
TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE
FROM HISTORY WHERE
STORAGE_GB IS NOT NULL AND
APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
ORDER BY STORAGE_GB DESC
FETCH NEXT 10 ROWS ONLY
More info: http://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html
更多信息：http: //docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html
回答by APC
With regards to the poor performance there are any number of things it could be, and it really ought to be a separate question. However, there is one obvious thing that could be a problem:
关于糟糕的表现，可能有很多事情，这真的应该是一个单独的问题。但是，有一件显而易见的事情可能是个问题：
WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009')
If HISTORY_DATE really is a date column and if it has an index then this rewrite will perform better:
如果 HISTORY_DATE 确实是一个日期列并且它有一个索引，那么这个重写会表现得更好：
WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')
This is because a datatype conversion disables the use of a B-Tree index.
这是因为数据类型转换禁用了 B 树索引的使用。
回答by Shaaban
try
尝试
SELECT * FROM users FETCH NEXT 10 ROWS ONLY;
回答by vijaya
You get an apparently random set because ROWNUM is applied before the ORDER BY. So your query takes the first ten rows and sorts them.0 To select the top ten salaries you should use an analytic function in a subquery, then filter that:
你得到一个明显随机的集合，因为 ROWNUM 在 ORDER BY 之前应用。因此，您的查询采用前十行并对它们进行排序。0 要选择前十名的薪水，您应该在子查询中使用分析函数，然后过滤：
select * from
(select empno,
ename,
sal,
row_number() over(order by sal desc nulls last) rnm
from emp)
where rnm<=10
