SQL Oracle：使用“order by”子句显示行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1377825/
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
Oracle: Display row number with 'order by' clause
提问by Igor Konoplyanko
I wonder how could i print a row number for sql statement where is using order.
Currently i tried ROWNUMbut as i understand it works only for unsorted result set.
我想知道如何为正在使用订单的 sql 语句打印行号。目前我尝试过，ROWNUM但据我所知，它仅适用于未排序的结果集。
SELECT rownum, a.lg_id, a.full_name, a.sort_order
FROM activity_type_lang a
where a.lg_id = 'en'
order by a.full_name;
TIA
TIA
回答by Justin Cave
In addition to nesting the query, you can use an analytic function
除了嵌套查询，您还可以使用分析函数
SELECT row_number() OVER (ORDER BY a.full_name),
lg_id,
full_name,
sort_order
FROM activity_type_lang a
WHERE a.lg_id = 'en'
ORDER BY a.full_name
Using analytic functions also makes it easier if you want to change how ties are handled. You can replace ROW_NUMBERwith RANK or DENSE_RANK.
如果您想更改关系的处理方式，使用分析函数也更容易。您可以替换ROW_NUMBER为 RANK 或DENSE_RANK。
回答by Igor Konoplyanko
Oh. Seems i've found already a solution.
哦。似乎我已经找到了解决方案。
Select rownum, lg_id, full_name, sort_order from
(SELECT a.lg_id, a.full_name, a.sort_order
FROM activity_type_lang a
where a.lg_id = 'en'
order by a.full_name);
回答by Igor Konoplyanko
rownum is applied before ordering, so you have to rewrite your query like this: select rownum, xxx.* from ( SELECT a.lg_id, a.full_name, a.sort_order FROM activity_type_lang a where a.lg_id = 'en' order by a.full_name ) xxx;
rownum 在排序之前应用，所以你必须像这样重写你的查询： select rownum, xxx.* from ( SELECT a.lg_id, a.full_name, a.sort_order FROM activity_type_lang a where a.lg_id = 'en' order by a .full_name ) xxx;
回答by Svetlozar Angelov
Hello. I wonder how could i print a row number for sql statement where is using order. Currently i tried ROWNUM but as i understand it works only for unsorted result set.
你好。我想知道如何为正在使用订单的 sql 语句打印行号。目前我尝试过 ROWNUM 但据我所知它仅适用于未排序的结果集。
To be clear (somebody might get it wrong). It doeswork (but not in the way you expect). The problem with it is that it "attaches" the ROWNUM before the sortand you get your records but not in consecutive ROWNUM records. Why? Because
要清楚（有人可能会弄错）。它确实有效（但不是您期望的方式）。它的问题在于它在排序之前“附加”了 ROWNUM ，你得到了你的记录，但不是连续的 ROWNUM 记录。为什么？因为
The first record that meets the where criteria in a select statement is given rownum=1
满足 select 语句中 where 条件的第一条记录被赋予 rownum=1
This is really good example how select / order mechanism works.
这是选择/排序机制如何工作的一个很好的例子。
