如何从预言机列中的序列号中删除间隙

Question

现在表结构和数据如下所示：

Name      record_number        Area
a         1                    CN
a         3                    DE
a         4                    FR
a         6                    DE
b         1                    DE
b         2                    CN

主键是（名称、record_number）

record_nubmer是由 max（record_number） + 1 生成的，假设有两条记录被 record_number = 2/5 和 name = 'a' 删除，所以谁能告诉我删除后如何删除上表中record_nubmer间隙，最终结果应该是这样的：

Name      record_number        Area
a         1                    CN
a         2                    DE
a         3                    FR
a         4                    DE
b         1                    DE
b         2                    CN

Answer 1

您可以使用

delete命令更新记录，如下所示：

update my_table set record_number = record_number - 1
where name = :old.name and record_number > :old.record_number

我不确定您是否可以创建此触发器（而且我现在无法访问 Oracle 部署）。

尝试创建这样的触发器（删除每一行后），如果没有，请更新应用程序，以便在每次删除后执行上述查询。

另一种解决方案是，执行以下查询：

select name, record_number, rank() over (partition by name order by record_number) new_record_number
  from my_table

现在遍历结果，并执行：

update my_table
   set record_number = :new_record_number
 where name = :name and
       record_number = :record_number

整个事情可以成为一个存储过程，然后在每个删除命令之后调用它。

Answer 2

受到@AmirPashazadeh rank()的启发，这里有一个单行解决方案：

UPDATE my_table t3
  SET record_number=(SELECT
   t2.rank
   FROM my_table t1
   INNER JOIN (SELECT
    name,
    record_number,
    rank() over (partition by name ORDER BY record_number) rank
    FROM my_table) t2 ON (t1.name=t2.name AND t1.record_number=t2.record_number)
   WHERE t1.name=t3.name AND t1.record_number=t3.record_number);

SQL 小提琴演示