oracle 中的前 10 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35689276/
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
top 10 rows in oracle
提问by dhruv kadia
i have 2 tables . abc(CID(pk), cname,) order(order_id(pk), CID(fk), number_of_rentals)
我有 2 张桌子。abc(CID(pk), cname,) order(order_id(pk), CID(fk), number_of_rentals)
i want to fetch top 10 customers based on number of rentals.
我想根据租赁数量获取前 10 位客户。
SELECT cid, sum(no_rentals) as sum
FROM orders
group by cid, no_rentals
order by no_rentals desc;
how can i use rownum function in above query to fetch the desired output
我如何在上面的查询中使用 rownum 函数来获取所需的输出
回答by MT0
Just wrap your query in:
只需将您的查询包装在：
SELECT * FROM ( your_query ) WHERE ROWNUM <= 10;
However, your query does not look like it is going to do what you intend as the GROUP BY no_renaltswill mean that each distinct no_rentalsvalue will be in its own group and you will not sum the values for each customer so you probably don't want to include it in the GROUP BY. Also, if you want to order by the total number of rentals then you want to ORDER BY SUM( no_rentals )(or by its alias) like this:
但是，您的查询看起来不会按照您的意图执行，因为这GROUP BY no_renalts意味着每个不同的no_rentals值都将在其自己的组中，并且您不会对每个客户的值求和，因此您可能不想包含它在GROUP BY. 此外，如果您想按租赁总数订购，那么您想ORDER BY SUM( no_rentals )（或按其别名）像这样：
SELECT cid,
SUM(no_rentals) as total_no_rentals
FROM orders
GROUP BY cid
ORDER BY total_no_rentals DESC;
Then you can apply the row limit like this:
然后你可以像这样应用行限制：
SELECT *
FROM (
SELECT cid,
SUM(no_rentals) as total_no_rentals
FROM orders
GROUP BY cid
ORDER BY total_no_rentals DESC
)
WHERE ROWNUM <= 10;
