Oracle 查询中的自增值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15881419/
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
Self Incrementing Value In Oracle Query
提问by a_horse_with_no_name
I'm new to Oracle DB...
我是 Oracle 数据库的新手...
I have a table consisting of 2 fields, YEAR and MAINT_CODE.
我有一个由 2 个字段组成的表，YEAR 和 MAINT_CODE。
I have to write a report query which will render the following in a report:
我必须编写一个报告查询，它将在报告中呈现以下内容：
ACTION | YEAR | MAINT_CODE
--------------------------
Action 1 | 2012 | 0123
Action 2 | 2012 | 0122
Action 3 | 2013 | 0122
Now, I do not have an Action field, this number after Action must just increment. I cannot alter the table at all.
现在，我没有 Action 字段，Action 之后的这个数字必须递增。我根本不能改变桌子。
My current query looks like this:
我当前的查询如下所示：
SELECT YEAR, MAINT_CODE
FROM mytable
How can I get that incrementing value after the Action? I'm not even sure where to look - I've looked into sequencing but I'm not sure that's what I need here. Any ideas?
如何在 Action 之后获得递增的值？我什至不确定在哪里看 - 我已经研究过排序，但我不确定这就是我在这里需要的。有任何想法吗？
回答by a_horse_with_no_name
SELECT 'Action '||to_char(row_number() over (order by year, maint_code)) as action,
YEAR,
MAINT_CODE
FROM mytable
order by year, maint_code
You have to have an order otherwise the "sequence" does not make sense, that's why I have applied an order by year, maint_codeto generate the row_number()and sort the overall result with the same sort definition.
您必须有一个订单，否则“序列”没有意义，这就是为什么我应用 anorder by year, maint_code来生成row_number()并使用相同的排序定义对整体结果进行排序。
