SQL Oracle 如何选择前 100 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27034585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Select Top 100 rows in Oracle?
提问by user2321728
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.
我写了一个如下查询来获取每个客户的最新订单。内部查询工作正常。但我不知道如何根据结果获得前 100 个。
SELECT * FROM (
SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn
FROM order
) WHERE rn=1
Any ideas? Thanks.
有任何想法吗？谢谢。
回答by Frank Schmitt
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 包含订单创建的时间，并且您希望 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
- 在最里面的查询中添加 create_time
- 通过以下命令对外部查询的结果进行排序
create_time desc - 添加一个最外层查询，该查询使用以下方法过滤前 100 行
ROWNUM
Query:
询问：
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
UPDATE for Oracle 12c
Oracle 12c 的更新
With release 12.1, Oracle introduced "real" Top-N queries. Using the new FETCH FIRST...syntax, you can also use:
在 12.1 版中，Oracle 引入了“真正的”Top-N 查询。使用新FETCH FIRST...语法，您还可以使用：
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)
回答by Moneer Kamal
you should use rownum in oracle to do what you seek
你应该在 oracle 中使用 rownum 来做你想要的
where rownum <= 100
see also those answers to help you
另请参阅这些答案以帮助您
回答by Usama
As Moneer Kamal said, you can do that simply:
正如 Moneer Kamal 所说，您可以简单地做到这一点：
SELECT id, client_id FROM order
WHERE rownum <= 100
ORDER BY create_time DESC;
Notice that the ordering is done aftergetting the 100 row. This might be useful for who does not want ordering.
请注意，在获得 100 行后完成排序。这对于不想订购的人可能很有用。
回答by Lorenzo
First 10 customers inserted into db (table customers):
插入数据库的前 10 个客户（表客户）：
select * from customers where customer_id <=
(select min(customer_id)+10 from customers)
Last 10 customers inserted into db (table customers):
select * from customers where customer_id >=
(select max(customer_id)-10 from customers)
Hope this helps....
希望这可以帮助....
回答by Sarz
Try this:
尝试这个：
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) alias_name
WHERE rownum <= 100
ORDER BY rownum;
Or TOP:
或顶：
SELECT TOP 2 * FROM Customers; //But not supported in Oracle
NOTE:I suppose that your internal query is fine. Please share your output of this.
注意：我想您的内部查询没问题。请分享您的输出。
