Oracle SELECT TOP 10 记录

2021-12-01 00:00:00 sql oracle top-n

我对 Oracle 中的 SQL 语句有一个大问题.我想选择 STORAGE_DB 排序的前 10 条记录，这些记录不在其他选择语句的列表中.

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.

这个适用于所有记录:

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.请参阅this了解更多信息.

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.

相关文章