oracle 如何选择查询结果的特定行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37959307/
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 a particular row of query result
提问by user286009
I have a following table in my Oracle database:
我的 Oracle 数据库中有一个下表：
How can I select the course which is done by most of the students? I am trying multiple variations in following SQL query but it's not working -
如何选择大多数学生都完成的课程？我在以下 SQL 查询中尝试了多种变体，但它不起作用 -
select count(course) as pcourse, course
from studies
group by course
order by pcourse dec;
回答by aakashgupta.0205
Since this is an Oracle DB, i think the following should work
由于这是一个 Oracle DB，我认为以下应该有效
SELECT * FROM
(select count(course) as pcourse, course
from studies
group by course
order by pcourse dec)
WHERE ROWNUM <= 1
Reply to the Comment
回复评论
That's because rownum is assigned sequentiallyto the rows that are returned.
那是因为 rownum 被顺序分配给返回的行。
So let's say you put ROWNUM = 2, your query will return the first row, and temporarily give it rownum of 1. But since it does not match the condition ROWNUM = 2, it will be discarded. Then it will go on to fetch the second row, and give it also rownum 1, and the cycle continues.
所以假设你把 ROWNUM = 2，你的查询将返回第一行，并暂时给它 rownum 1。但由于它不匹配条件 ROWNUM = 2，它将被丢弃。然后它将继续获取第二行，并为其提供 rownum 1，然后循环继续。
If you want to get the Nth row, you could probably do this
如果你想得到第 N 行，你可能会这样做
SELECT pcourse, course FROM
(select count(course) as pcourse, course, rownum as rn
from studies
group by course
order by pcourse dec)
WHERE rn = N
where N is the required row.
其中 N 是所需的行。
回答by Radu Gheorghiu
You need to use rownum, which is Oracle's equivalent of TOP 1(from SQL Server) or LIMIT 1(from MySQL).
您需要使用rownum，它相当于 Oracle 的TOP 1（来自 SQL Server）或LIMIT 1（来自 MySQL）。
select *
from
(select count(course) as pcourse
, course
from studies
group by course
order by pcourse desc) result_set
where rownum = 1
It is important that you use where rownum = 1in a higher level SELECTbecause Oracle requires you to first sort the result set and then it will filter the rows.
where rownum = 1在更高级别使用很重要，SELECT因为 Oracle 要求您首先对结果集进行排序，然后它会过滤行。
Otherwise, if you would include the where rownum = 1condition in the same level as the SELECTwith which you get your data, then it will be immediately be filtering to 1 row (the first it finds) and sorting it (which won't make any sense, since it will only be one row).
否则，如果您将where rownum = 1条件包含在SELECT与获取数据的级别相同的级别中，那么它将立即过滤到 1 行（它找到的第一行）并对其进行排序（这没有任何意义，因为它只会是一排）。
As per @a_horse_with_no_name's comment, apparently Oracle gives you the possibility to write a TOP 1filtering condition in the same level SELECT, like below:
根据@a_horse_with_no_name的评论，显然Oracle 为您提供了TOP 1在同一级别编写过滤条件的可能性SELECT，如下所示：
select count(course) as pcourse
, course
from studies
group by course
order by pcourse desc
fetch first 1 rows only;
回答by Matt
This should work but I haven't created your test data because it is in a picture, please include as text next time
这应该可行，但我还没有创建您的测试数据，因为它在图片中，请下次作为文本包含
SELECT
course
,COUNT(*) AS pcourse
FROM
Studies
GROUP BY
course
ORDER BY
COUNT(*) DESC
FETCH FIRST ROW ONLY
I am still a little unsure of how to the fetch first row/select top 1/limit 1 for Oracle but @a-horse_with_no_name is teaching and I am learning so here is stab at it. here is a link for Oracles syntax on offestand fetchhttp://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html
我仍然有点不确定如何为 Oracle 获取第一行/选择前 1/限制 1，但 @a-horse_with_no_name 正在教学，我正在学习，所以在这里尝试一下。这是 Oracles 语法的链接offest和fetchhttp://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html
