PostgreSQL：为每个图书引用生成唯一的作者序号

嘿，我完全get到你的需求了——要给每个图书关联的作者生成从1开始的唯一序号，而且序号得按作者姓名排序来生成对吧？之前用sequence不行是因为它是全局计数的，没法按图书重置序号，而ROW_NUMBER()窗口函数正好能完美搞定这个需求！

第一步：先验证正确的序号生成逻辑 #

先写个查询语句确认结果是否符合你的预期：

SELECT 
  author_id,
  fullname,
  mobileno,
  fk_book_id,
  ROW_NUMBER() OVER (PARTITION BY fk_book_id ORDER BY fullname) AS authororder
FROM author;

这个语句里的核心关键点：

• PARTITION BY fk_book_id：把数据按图书ID拆分成独立的分组，每个分组单独计算序号
• ORDER BY fullname：在每个图书组内，按作者姓名的字母顺序排序，以此决定序号的先后顺序
• ROW_NUMBER()：在每个分组内自动生成从1开始的连续整数，保证每个图书下的序号唯一且递增

运行这个查询，得到的结果会和你给出的预期完全匹配：

author_id fullname           mobileno fk_book_id authororder
100       John Green         30303    1          1
102       Lauren Myracle     76665    1          2
101       Maureen Johnson    4343     1          3
104       David Oliver Relin 72322    2          1
103       Greg Mortenson     6434     2          2
106       Angus MacGyver     27234    3          1
108       Anonymous          55554    3          2
105       Marco Polo         54321    3          3
107       Timo TA            83451    3          4

第二步：更新表中的authororder列 #

如果要把这个正确的序号写入到author表的authororder字段里，可以用CTE（公共表表达式）结合UPDATE语句来实现：

WITH ranked_authors AS (
  SELECT 
    author_id,
    ROW_NUMBER() OVER (PARTITION BY fk_book_id ORDER BY fullname) AS new_authororder
  FROM author
)
UPDATE author
SET authororder = new_authororder
FROM ranked_authors
WHERE author.author_id = ranked_authors.author_id;

执行这个语句后，你的author表中的authororder列就会被正确填充啦。

为什么sequence不适用？ #

sequence是全局生成的递增序列，它没办法识别不同的图书分组，所以没法在每个图书下重置计数从1开始，这就是它满足不了你需求的核心原因。而窗口函数的PARTITION BY特性正好解决了分组重置计数的问题。

内容的提问来源于stack exchange，提问作者Yorma