sql - 删除行后重置列的数字序列

Question

我已经看到了许多在删除一行后重新设置标识列的解决方案。但是，我需要从不是标识列的列中重置数字序列。

+------+---------+----------+
| group| user    | sequence |
+------+---------+----------+
|   1  | barney  |     1    |
|   1  | andy    |     2    |
|   1  | odis    |     3    |
|   1  | opie    |     4    |
|   2  | beaver  |     1    |
|   2  | wally   |     2    |
|   2  | eddie   |     3    |
+------+---------+----------+

因此，如果我删除 odis，新序列需要如下所示：

+------+---------+----------+
| group| user    | sequence |
+------+---------+----------+
|   1  | barney  |     1    |
|   1  | andy    |     2    |
|   1  | opie    |     3    |
|   2  | beaver  |     1    |
|   2  | wally   |     2    |
|   2  | eddie   |     3    |
+------+---------+----------+

标签： sqlsql-server

Answer 1

我希望这可以解决您的问题：

with tbl2(grp, usr, seq) as
(
select grp, usr,
    row_number() over(partition by grp order by seq) as seq
from tbl1
)
update tbl1
set seq = t2.seq
from tbl1 t1
join tbl2 t2 on t2.grp = t1.grp and t2.usr = t1.usr
where t2.seq != t1.seq