在 Postgresql 中使用标识符重新排序列

发布于 2024-11-08 12:26:38 字数 462 浏览 3 评论 0原文

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

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。

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

任何人都可以帮忙吗？

原文

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

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: Add sequence column based on another field

Can anyone help?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情痴 2024-11-15 12:26:38

如果内存可用，row_number() 返回其自己分区内的数字。换句话说，row_number() over (partition by name order BY name) 将为除重复项之外的每行返回 1。您可能需要 rank() 而不是（按名称排序）。

经过长时间的讨论：

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;

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.

After a long discussion:

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;

回复收藏 0 原文

放手` 2024-11-15 12:26:38

这有效！（请参阅我的 OP 链接到之前的 MySql 链接。在 Postgresql 中，它无需临时表即可工作）

alter table telephn_table add column tid integer default 0;
UPDATE telephn_table set tid=(SELECT count(*)+1 from telephn_table t where t.sid < telephn_table.sid and telephn_table.name=t.name)

回复收藏 0 原文

~没有更多了~