填充SQL行中的序列 [英] Fill sequence in sql rows

问题描述

我有一个表，该表存储一组属性并按顺序保持它们的顺序.有可能从表中删除其中一个属性(行)，并且应该压缩位置序列.

I have a table that stores a group of attributes and keeps them ordered in a sequence. The chance exists that one of the attributes (rows) could be deleted from the table, and the sequence of positions should be compacted.

例如，如果我最初具有以下一组值:

For instance, if I originally have these set of values:

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    |   1 |
|  2 | two    |   2 |
|  3 | three  |   3 |
|  4 | four   |   4 |
+----+--------+-----+

第二行被删除，所有后续行的位置应更新以缩小差距.结果应该是这样:

And the second row was deleted, the position of all subsequent rows should be updated to close the gaps. The result should be this:

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    |   1 |
|  3 | three  |   2 |
|  4 | four   |   3 |
+----+--------+-----+

有没有办法在单个查询中进行此更新?我该怎么办?

Is there a way to do this update in a single query? How could I do this?

PS:由于该系统应该支持两个引擎，所以我很欣赏SQLServer和Oracle的示例.谢谢！

PS: I'd appreciate examples for both SQLServer and Oracle, since the system is supposed to support both engines. Thanks!

更新:其原因是允许用户随意修改职位，以及添加或删除新行.位置会显示给用户，因此，这些位置应始终显示一致性序列(并且该序列必须存储，而不是按需生成).

UPDATE: The reason for this is that users are allowed to modify the positions at will, as well as adding or deleting new rows. Positions are shown to the user, and for that reason, these should show a consistence sequence at all times (and this sequence must be stored, and not generated on demand).

推荐答案

不确定它是否有效，但是对于Oracle，我会尝试以下操作:

Not sure it works, But with Oracle I would try the following:

update my_table set pos = rownum;

这篇关于填充SQL行中的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！