oracle PL/SQL Select 结果中的增量变量/计数器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9736638/
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
Increment Variable/Counter in PL/SQL Select result
提问by Luka
A rather silly question. I have a Oracle Db with Products and now read out the first 10 Products.
一个比较愚蠢的问题。我有一个包含产品的 Oracle 数据库，现在读出前 10 个产品。
I now want the following display
我现在想要以下显示
1 Product A
2 Product XY
3 Product B
Stupid questions, but how to get the counter in front? I obviously have to increment, but I don't understand how that works. I also thought to work with WITH and tmp table, but can not figure out how that needs to be set up.
愚蠢的问题，但如何让柜台在前面？我显然必须增加，但我不明白这是如何工作的。我也想过使用 WITH 和 tmp 表，但无法弄清楚需要如何设置。
SELECT POS ???, PRODUCTNAME FROM TBLPRODUCT
I am not very familiar with PL/SQL. Can someone give me a hint? Thanks so much.
我对 PL/SQL 不是很熟悉。有人可以给我一个提示吗？非常感谢。
回答by Dave Costa
ROWNUMis one approach as shown by Bob, but if you are using more complicated queries -- especially if you are ordering the rows explicitly -- it may not give the results you want.
ROWNUM是 Bob 所示的一种方法，但是如果您使用更复杂的查询——尤其是如果您显式地对行进行排序——它可能不会给出您想要的结果。
Nowadays, analytic functions are generally a better approach as you have explicit control over the ordering:
如今，分析函数通常是一种更好的方法，因为您可以明确控制顺序：
SELECT ROW_NUMBER() OVER (ORDER BY productname), productname
FROM tableproduct
ORDER BY productname
Note that the ordering of the rows which determines the row numbers is separate from the ordering of the overall result set. In this example I've used the same ordering as that's what you're likely to want, but it's worth noting that this gives you more flexibility.
请注意，确定行号的行排序与整个结果集的排序是分开的。在本示例中，我使用了与您可能想要的相同的顺序，但值得注意的是，这为您提供了更大的灵活性。
(And apologies for being a little pedantic, but this has nothing to do with PL/SQL, which is the procedural language embedded in Oracle. This is simply about Oracle's implementation of SQL.)
（抱歉有点迂腐，但这与 PL/SQL 无关，PL/SQL 是嵌入在 Oracle 中的过程语言。这只是关于 Oracle 对 SQL 的实现。）
回答by Bob Jarvis - Reinstate Monica
Use ROWNUM, as in
使用 ROWNUM，如
SELECT ROWNUM, PRODUCTNAME FROM TBLPRODUCT
Share and enjoy.
分享和享受。
