SQL 如何从 Oracle 中的选定行中获取行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14557751/
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 get row number from selected rows in Oracle
提问by Tushar
I am selecting few rows from database e.g.:
我从数据库中选择几行，例如：
select * from student where name is like %ram%
Result:
结果：
ID Name email Branch
7 rama [email protected] B1
5 ramb [email protected] B2
3 ramc [email protected] B3
8 ramd [email protected] B4
11 rame [email protected] B5
12 ramf [email protected] B6
14 ramg [email protected] B7
I need to get row number for which branch is B5. Expected value is "5"
我需要获取分支为 B5 的行号。预期值为“5”
Can someone please suggest How to implement this in query ?
有人可以建议如何在查询中实现这一点吗？
回答by Ben
There is no inherent ordering to a table. So, the row number itself is a meaningless metric.
表没有固有的顺序。因此，行号本身是一个毫无意义的指标。
However, you can get the row number of a result setby using the ROWNUMpsuedocolumn or the ROW_NUMBER()analytic function, which is more powerful.
但是可以通过ROWNUMpsuedocolumn或者解析函数获取结果集的行号，功能更强大。ROW_NUMBER()
As there is no ordering to a table both require an explicit ORDER BY clause in order to work.
由于没有对表进行排序，因此两者都需要显式 ORDER BY 子句才能工作。
select rownum, a.*
from ( select *
from student
where name like '%ram%'
order by branch
) a
or using the analytic query
或使用分析查询
select row_number() over ( order by branch ) as rnum, a.*
from student
where name like '%ram%'
Your syntax where name is like ...is incorrect, there's no need for the IS, so I've removed it.
您的语法where name is like ...不正确，不需要 IS，所以我已将其删除。
The ORDER BY here relies on a binary sort, so if a branch starts with anything other than B the results may be different, for instance bis greater than B.
这里的 ORDER BY 依赖于二进制排序，因此如果分支以 B 以外的任何内容开头，结果可能会有所不同，例如b大于B。
回答by DazzaL
you can just do
你可以做
select rownum, l.* from student l where name like %ram%
this assigns the row number as the rows are fetched (so no guaranteed ordering of course).
这会在获取行时分配行号（因此当然不能保证排序）。
if you wanted to order first do:
如果您想先订购，请执行以下操作：
select rownum, l.*
from (select * from student l where name like %ram% order by...) l;
回答by boyce
I think using
我认为使用
select rownum st.Branch
from student st
where st.name like '%ram%'
is a simple way; you should add single quotes in the LIKE statement. If you use row_number(), you should add over (order by 'sort column' 'asc/desc'), for instance:
是一种简单的方法；您应该在 LIKE 语句中添加单引号。如果使用row_number()，则应添加over (order by 'sort column' 'asc/desc')，例如：
select st.branch, row_number() over (order by 'sort column' 'asc/desc')
from student st
where st.name like '%ram%'
回答by Srinivasan.S
The below query helps to get the row number in oracle,
以下查询有助于获取 oracle 中的行号，
SELECT ROWNUM AS SNO,ID,NAME,EMAIL,BRANCH FROM student WHERE NAME LIKE '%ram%';
