sql - Oracle SELECT TOP 10记录(Oracle SELECT TOP 10 records)

Question

I have an big problem with an SQL Statement in Oracle.

(我在Oracle中使用SQL语句遇到了很大的问题。)

I want to select the TOP 10 Records ordered by STORAGE_DB which aren't in a list from an other select statement.

(我想选择STORAGE_DB排序的前10条记录，这些记录不在其他select语句的列表中。)

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条记录）)

  ask by opHASnoNAME translate from so

Answer 1

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 rownum to the result after it has been returned.

(返回结果后，Oracle将rownum应用于结果。)
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.

(您也可以使用RANK（）函数获取Top-N结果。)

For performance try using NOT EXISTS in place of NOT IN .

(为了提高性能，请尝试使用NOT EXISTS代替NOT IN 。)

See this for more.

(见这更多。)