在 Postgresql 中重新排序带有标识符的列 [英] Resequencing a column with identifier in Postgresql

问题描述

以下代码工作并创建一个带有序列号的临时表，该表为每个新名称重新启动:

The following code works and creates a temporary table with a sequence number which is restarted for every new name:

with results as (select row_number() over (partition by name order BY name) as mytid,name from telephn_table)
select * from results order by name

然而，我的目标是将新序列号永久插入电话表中.如何将新的序列号从结果表转移到电话表?我遇到了 MySql 的以下内容，但无法将其转换为 Postgresql.

My objective however is to insert the new sequence number permanently into the telephone table. How do I transfer the new sequence number from the results table to the telephone table? I have come across the following for MySql but was not able to convert it to Postgresql.

MySQL:基于另一个字段添加序列列

有人可以帮忙吗?

推荐答案

如果没记错，row_number() 返回其自己分区内的数字.换句话说，row_number() over (partition by name order BY name) 将为除重复项之外的每一行返回 1.您可能希望 rank() over (order by name) 代替.

If memory serves, row_number() returns the number within its own partition. In other words, row_number() over (partition by name order BY name) would return 1 for each row except duplicates. You likely want rank() over (order by name) instead.

经过长时间的讨论:

update telephn_table
set sid = rows.new_sid
from (select pkey,
             row_number() over (partition BY name) as new_sid,
             name
      from telephn_table
      ) as rows
where rows.pkey = telephn_table.pkey;

这篇关于在 Postgresql 中重新排序带有标识符的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！