Oracle SELECT TOP 10记录 [英] Oracle SELECT TOP 10 records
问题描述
我在Oracle中的SQL语句有很大的问题.我想选择STORAGE_DB排序的前10条记录,这些记录不在其他select语句的列表中.
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.
此记录适用于所有记录:
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')
但是当我添加
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.
有人有一个好的解决方案吗?另一个问题:该查询的速度非常慢(超过10k条记录)
Does someone has an good solution? The other problem: This query is realy slow (10k+ records)
推荐答案
您需要将当前查询放入子查询中,如下所示:
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返回结果后,将 rownum 应用于结果.
您需要在返回结果后对其进行过滤,因此需要一个子查询.您还可以使用 RANK()函数来获取Top-N结果.
为了提高性能,请尝试使用NOT EXISTS
代替NOT IN
.有关更多信息,请参见此.
Oracle applies rownum to 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 EXISTS
in place of NOT IN
. See this for more.
这篇关于Oracle SELECT TOP 10记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!