如何在 Oracle SQL 中将结果限制为 1 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41220882/
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 limit the results to 1 row in Oracle SQL
提问by Satwik
I've this query which runs successfuly
我有这个成功运行的查询
SELECT customerNumber
FROM ORDERS
GROUP BY customerNumber
ORDER BY count(orderNumber) DESC
But when I try to limit the number of rows returned to 1, I get the following error
但是当我尝试将返回的行数限制为 1 时，出现以下错误
ORA-00933: SQL command not properly ended
Here's what I've tried :
这是我尝试过的：
SELECT customerNumber
FROM ORDERS
GROUP BY customerNumber
ORDER BY count(orderNumber) DESC
fetch first 1 row only;
and
和
SELECT customerNumber
FROM ORDERS
GROUP BY customerNumber
ORDER BY count(orderNumber) DESC
WHERE ROWNUM=1;
采纳答案by Radu Gheorghiu
In Oracle you need to do the ordering first and then select rownum. Thus, you need to nest the query which returns the sorted data and take the filtering WHEREclause outside.
在 Oracle 中，您需要先进行排序，然后选择rownum。因此，您需要嵌套返回排序数据的查询并将过滤WHERE子句放在外面。
SELECT * FROM
(
SELECT customerNumber
FROM ORDERS
GROUP BY customerNumber
ORDER BY count(orderNumber) DESC
) resultSet
WHERE ROWNUM=1;
回答by a_horse_with_no_name
You can combine grouping and window functions to accomplish this.
您可以结合分组和窗口函数来完成此操作。
select customernumber, num_orders
from (
SELECT customerNumber,
count(*) as num_orders,
dense_rank() over (order by count(*) desc) as rnk
from orders
group by customerNumber
) t
where rnk = 1;
The difference to a simple "get me only one row" is that this will also return multiple customers that have the same number of orders. If you don't want that, replace dense_rank()with row_number()
与简单的“只给我一行”不同的是，这也将返回具有相同订单数量的多个客户。如果您不想要那样，请替换dense_rank()为row_number()
回答by scaisEdge
the where condition must be placed before the order by (but seem that you need the first row after the sorted is done )
where 条件必须放在 order by 之前（但似乎您需要排序完成后的第一行）
so you should use a select this wya
所以你应该使用 select this wya
select * from (
SELECT customerNumber
FROM ORDERS
GROUP BY customerNumber
ORDER BY count(orderNumber) DESC
) t
WHERE ROWNUM=1;
