在sql行中填充序列

Question

我有一个表，用于存储一组属性并使它们按顺序排列。有可能从表中删除其中一个属性（行），并且应该压缩位置序列。

例如，如果我最初有这些值集：

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

并且第二行被删除，则应更新所有后续行的位置以缩小间隙。结果应该是这样的：

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

有没有办法在单个查询中执行此更新？我怎么能这样做呢？

PS：我希望能提供 SQLServer 和 Oracle 的示例，因为系统应该支持这两种引擎。谢谢！

更新：原因是用户可以随意修改位置，以及添加或删除新行。位置向用户显示，因此，这些位置应始终显示一致的序列（并且该序列必须存储，而不是按需生成）。

Answer 1

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

update my_table set pos = rownum;

Answer 2

这可行，但对于大型数据集可能不是最佳的：

SQL> UPDATE my_table t
  2     SET pos = (SELECT COUNT(*) FROM my_table WHERE id <= t.id);

3 rows updated

SQL> select * from my_table;

        ID NAME              POS
---------- ---------- ----------
         1 one                 1
         3 three               2
         4 four                3

Answer 3

您真的需要序列值是连续的，还是只需要能够显示连续值？最简单的方法是让实际序列变得稀疏，并根据顺序计算排名：（

select id, 
       name, 
       dense_rank() over (order by pos) as pos,
       pos as sparse_pos 
from my_table

注意：这是 Oracle 特定的查询）

如果你一开始就使位置稀疏，这甚至会使重新排序- 订购更容易，因为您可以将每个新位置设置在两个现有位置的中间。例如，如果您有一个像这样的表：

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    | 100 |
|  2 | two    | 200 |
|  3 | three  | 300 |
|  4 | four   | 400 |
+----+--------+-----+

当需要将 ID 4 移动到位置 2 时，您只需将位置更改为 150。

---

进一步说明：

使用上面的示例，用户最初会看到以下内容（因为您正在屏蔽位置）：

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

当用户通过您的界面指示位置 4 中的记录需要移动到位置 2 时，您将 ID 4 的位置更新为 150，然后重新运行查询。用户会看到这一点：

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

这不起作用的唯一原因是用户直接在数据库中编辑数据。不过，即使在这种情况下，我也倾向于通过视图和替代触发器来使用这种解决方案。