如何在Oracle中选择前100行? [英] How to Select Top 100 rows in Oracle?

问题描述

我的要求是获取每个客户的最新订单，然后获取前100条记录.

My requirement is to get each client's latest order, and then get top 100 records.

我写了一个查询，如下所示，以获取每个客户的最新订单.内部查询工作正常.但是我不知道如何根据结果获得前100名.

I wrote one query as below to get latest orders for each client. Internal query works fine. But I don't know how to get first 100 based on the results.

    SELECT * FROM (
      SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
      FROM order
    ) WHERE rn=1

有什么想法吗?谢谢.

推荐答案

假设create_time包含创建订单的时间，并且您希望100个具有最新订单的客户，您可以:

Assuming that create_time contains the time the order was created, and you want the 100 clients with the latest orders, you can:

• 在最里面的查询中添加create_time
• 通过create_time desc
对外部查询的结果进行排序
• 添加最外部的查询，该查询使用
过滤前100行

• add the create_time in your innermost query
• order the results of your outer query by the create_time desc
• add an outermost query that filters the first 100 rows using ROWNUM

查询:

  SELECT * FROM (
     SELECT * FROM (
        SELECT 
          id, 
          client_id, 
          create_time,
          ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
        FROM order
      ) 
      WHERE rn=1
      ORDER BY create_time desc
  ) WHERE rownum <= 100

Oracle 12c的更新

从版本12.1开始，Oracle引入了真正的" Top-N查询.使用新的FETCH FIRST...语法，您还可以使用:

With release 12.1, Oracle introduced "real" Top-N queries. Using the new FETCH FIRST... syntax, you can also use:

  SELECT * FROM (
    SELECT 
      id, 
      client_id, 
      create_time,
      ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
    FROM order
  ) 
  WHERE rn = 1
  ORDER BY create_time desc
  FETCH FIRST 100 ROWS ONLY)

这篇关于如何在Oracle中选择前100行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！