Oracle查询前10条记录

Question

我是一名有用的助手，可以为您翻译文本。

我在Oracle中使用SQL语句遇到了一个大问题。我想选择按STORAGE_DB排序的前10个记录，并且这些记录不在另一个select语句的列表中。

对于所有记录，以下语句都可以正常工作：

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

我得到了一些“随机”的记录。我认为是因为限制在排序之前生效。

有人有好的解决方案吗？另一个问题：这个查询非常慢（10k+条记录）

- opHASnoNAME

Answer 1

您需要将当前查询作为子查询放置在以下位置：

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()函数来获取前 N 个结果。

为了提高性能，尝试使用NOT EXISTS代替NOT IN。详情请参见此处。

- Padmarag

---

在这种情况下，NOT EXISTS 不起作用（无效的关系运算符）APP_ID NOT EXISTS（SELEC...） - opHASnoNAME

有人可能会说，这很容易让人对Oracle失去兴趣。 - MrBoJangles

请查看下面关于 FETCH NEXT N ROWS ONLY 的答案。 - Mohnish

@Padmarag：当在查询中像这样使用rownum时 - Select * from SomeTable where someColumn = '123' and rownum <=3，它是在从[Select * from SomeTable where someColumn = '123']选择结果之后应用的吗？ - Farhan stands with Palestine

尝试使用以下查询语句以获取最大的10个存储GB数据记录：select * from (select distinct * from history order by storage_gb desc) where rownum < 10; --（修改内部查询以获取不同的记录） - Pavn

Answer 2

如果您正在使用Oracle 12c，请使用：

FETCH NEXT N ROWS ONLY

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

更多信息：http://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html

- Volpato

---

与其他答案相比，这是金子般的存在。 - aswzen

如果有人能够说明为什么这样做更好，那将是一个很好的评论/编辑。 - Chris

如果使用像SELECT * FROM TABLE1 WHERE ROWNUM<=10和SELECT * FROM TABLE1 FETCH NEXT 10 ROWS ONLY这样简单的查询，从执行计划来看，FETCH NEXT似乎更加耗费资源，因为它会执行谓词ROW_NUMBER() OVER ( ORDER BY NULL )<=10)。 - sanme98

Answer 3

尝试

SELECT * FROM users FETCH NEXT 10 ROWS ONLY;

- Shaaban

Answer 4

关于性能差的问题，可能有很多原因，而且这应该是一个单独的问题。不过，有一件明显的事情可能会引起问题:

WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009') 

如果HISTORY_DATE确实是一个日期列，并且它有一个索引，那么这个重写将会更加高效：

WHERE HISTORY_DATE = TO_DATE ('06.02.2009', 'DD.MM.YYYY')  

这是因为数据类型转换会禁用 B-Tree 索引的使用。

- APC

Answer 5

由于ROWNUM是在ORDER BY之前应用的，所以你得到了一个表面上随机的集合。因此，你的查询会取出前十行并对它们进行排序。为了选择前十个最高薪水，你应该在子查询中使用分析函数，然后过滤它：

 select * from 
     (select empno,
             ename,
             sal,
             row_number() over(order by sal desc nulls last) rnm
    from emp) 
 where rnm<=10

- vijaya

Answer 6

您可以使用此查询在Oracle中选择顶部记录。Rakesh B

select * from User_info where id >= (select max(id)-10 from User_info);

- Rakesh Bollu

---

嘿，Rakesh！我不确定这个答案是否完全回答了提出的问题。看看其他一些提供更多细节并且格式良好的响应，以获取一些示例。 - krfurlong