用于将列的编号重新排序为奇数或全部偶数的SQL查询

Lets say I have a column that has 20 rows (it's more, but the concept will hold true for 20). Right now, the column is uniquely numbered 1-20. I need to figure out a query though that will change that number scheme from sequential, to sequential with just even or odd numbers.

假设我有一个有20行的列(它更多,但概念将适用于20)。此时,该列的编号为1-20。我需要找出一个查询,虽然这会将数字方案从顺序更改为连续的偶数或奇数。

So for example I just need to create a column in a table where it is numbered in even numbers, or odd numbers. So after the update query is run, it would change my column data from 1-20 to 2-40 (even numbers only), or 1-39 (odd numbers only).

因此,例如,我只需要在表格中创建一个列,其中的列号以偶数或奇数编号。因此,在运行更新查询后,它会将我的列数据从1-20更改为2-40(仅限偶数)或1-39(仅限奇数)。

I'm sorry if that doesn't make sense, or if you can't think of a reason why I'd need that, but in a strange sort of way, I do need it.

我很抱歉,如果这没有意义,或者如果你想不出我需要它的原因,但是以一种奇怪的方式,我确实需要它。

EDIT:

Due to the fact that this doesn't make sense, I'll try and clarify a little more.

由于这没有意义,我会尝试澄清一点。

I have a column that is currently ordered 1-10

我有一个目前订购1-10的专栏

1,2,3,4,5,6,7,8,9,10.

I want to execute an update query on the column so that it looks like this:

我想在列上执行更新查询,使其如下所示:

1,3,5,7,9,11,13,15,17,19

2,4,6,8,10,12,14,16,18,20

Is there an update query that I could run to do that?

是否有可以运行的更新查询?

2 个解决方案

#1

For even numbers:

对于偶数:

UPDATE YourTable
   SET YourColumn = YourColumn * 2;

For odd numbers:

对于奇数:

UPDATE YourTable
   SET YourColumn = YourColumn * 2 - 1;

You can only run into trouble if your DBMS starts checking the column for uniqueness too soon, which is ultimately a flaw in (or limitation of) the DBMS. That is: while the 'even' update is running, there might be an as-yet-not-updated row containing value 2 as well as an already-updated row containing value 2 (originally 1). If the DBMS enforces uniqueness at each row, this will fail; but the DBMS is not supposed to do that.

如果您的DBMS过早地开始检查列的唯一性,那么您只会遇到麻烦,这最终是DBMS的一个缺陷(或限制)。也就是说:当'even'更新正在运行时,可能会有一个尚未更新的行包含值2以及一个已更新的行包含值2(最初为1)。如果DBMS在每一行强制执行唯一性,则会失败;但是DBMS不应该这样做。

#2

You can show your rows in any order you prefer.

您可以按自己喜欢的顺序显示行。

Ordering rows with even ids first:

首先使用偶数ID排序行:

SELECT *
FROM yourTable
ORDER BY id MOD 2
       , id

#1