oracle 在oracle中选择查询如何从1开始以2为增量添加数字列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41449068/
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
in oracle select query how to add a number column increment by 2 starting from 1
提问by siva
i need to write a oracle select query where i need a additional column which increment by 2 starting from 1.
我需要编写一个 oracle 选择查询，其中我需要一个从 1 开始增加 2 的附加列。
Example:
例子：
column1 column2
amit 1
siva 3
pyll 5
here from oracle table i can get only column1. but in query i have to generate column2. So my issue is dynamically get a column like rownum() and increment it by 2. is there any way to get such result. in mysql we can use session variables inside a query. i expect a similar kind of solution in oracle. but i couldn't find a simple query to generate such numbers.
这里从oracle表我只能得到column1。但在查询中我必须生成 column2。所以我的问题是动态获取像 rownum() 这样的列并将其增加 2. 有没有办法获得这样的结果。在 mysql 中，我们可以在查询中使用会话变量。我希望在 oracle 中有类似的解决方案。但我找不到一个简单的查询来生成这样的数字。
回答by Alex Poole
You know you have rownum available, but take a step back. You're starting with the contiguous sequence 1,2,3,4,5,6,... and you want to generate a sequence of odd numbers 1,3,5,7,9,11,.... So you need to figure out an algorithm that will convert one to the other.
您知道您有可用的 rownum，但退后一步。你从连续序列 1,2,3,4,5,6,... 开始，你想生成一个奇数序列 1,3,5,7,9,11,... 所以您需要找出一种将一种转换为另一种的算法。
If you say your starting number is nthen you want to generate mwhere m=(2*n)-1.
如果你说你的起始号码是n那么你想要生成mwhere m=(2*n)-1。
You can use rownum(or row_number(), etc.) to generate your nvalues:
您可以使用rownum（或row_number()等）来生成您的n值：
select column1, rownum as n
from your_table;
And you can then apply that algorithm:
然后您可以应用该算法：
select column1, (2*rownum)-1 as column2
from your_table;
COLUMN1 COLUMN2
------- ----------
amit 1
siva 3
pyll 5
jane 7
john 9
anna 11
...
With this simple approach the column2values are not in the same order as the column1values. You can either use row_number()or rank()instead, with a suitable order byclause; or use a subquery which does the ordering and apply rownum (and this algorithm) outside that:
通过这种简单的方法，column2值与值的顺序不同column1。您可以使用row_number()或rank()代替，使用合适的order by子句；或者使用一个子查询来进行排序并在它之外应用 rownum（和这个算法）：
select column1, (2*rownum)-1 as column2
from (
select column1
from your_name
order by column1
);
or some other variation, depending on the result you want to end up with.
或其他一些变化，这取决于您想要最终得到的结果。
