sql - PostgreSQL:创建一系列唯一的数字供引用

Question

我举了个例子: http://sqlfiddle.com/#!17/0f948/9

1-to-many 是我正在构建的，尽管对于这个例子来说多对多会更真实。

我需要完成的事情:我应该能够为作者表中的作者顺序列生成描述整数序列的作者顺序，从数字 1 开始，以便每个数字在书籍引用中都是唯一的。因此，对于某本书下的每位作者，引用数字从 1 到 2，到 3 等。还应生成 authororder 的编号以反射(reflect) author.fullname 的顺序(如:“select * from author order by fullname”)。

我尝试使用序列号，但并没有像我希望的那样重置每本书的序列号，而是数字继续增加(我猜应该是这样)。我也尝试在 row_number() 的帮助下这样做，但无法准备一个脚本来实现我想要的。

生成作者顺序后的最终结果是这样的:

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

Answer 1

你似乎想要:

row_number() over (partition by fk_book_id order by fullname) as authororder

您可以将其合并到更新中:

update author a
    set authororder = new_authororder
    from (select a.*, 
                 row_number() over (partition by fk_book_id order by fullname) as new_authororder
          from author a
         ) aa
    where a.authorid = aa.authorid;