mysql - 如何使用 mysql 脚本以跳过现有订单的方式生成行号作为订单

Question

我有一个如下表

+======+===========+=======+==========+
| team |    person | order | createdAt|
+======+===========+=======+==========+
|   A  |      John |       |2019/01/01|
+------+-----------+=======+==========+
|   B  |     Smith |       |2019/01/01|
+------+-----------+=======+==========+
|   A  |    Walter | 2     |2019/01/03|
+------+-----------+=======+==========+
|   A  |     Louis |       |2019/01/04|
+------+-----------+=======+==========+
|   C  | Elizabeth |       |2019/01/02|
+------+-----------+=======+==========+
|   B  |     Wayne |       |2019/01/02|
+------+-----------+=======+==========+

我想根据以下规则使用sql设置顺序

1. 根据团队排序
2. 按createdAt排序
3. 团队内的订单不应重复。例如，如果我们注意到 A 团队已经存在订单 2，我们应该使用下一个可用的数字 3 来填写订单。值得指出的是，同一团队中可能已经存在多个订单。例如，对于A队，如果我们只考虑createAt的顺序，应该是John - 1，Louis - 2，Walter - 3。但是，由于Walter已经被填充了2，所以Louis只能被分配3。

对于上面的例子，我期望的是

    +======+===========+=======+==========+
    | team |    person | order | createdAt|
    +======+===========+=======+==========+
    |   A  |      John | 1     |2019/01/01|
    +------+-----------+=======+==========+
    |   B  |     Smith | 1     |2019/01/01|
    +------+-----------+=======+==========+
    |   A  |    Walter | 2     |2019/01/03|
    +------+-----------+=======+==========+
    |   A  |     Louis | 3     |2019/01/02|
    +------+-----------+=======+==========+
    |   C  | Elizabeth | 1     |2019/01/02|
    +------+-----------+=======+==========+
    |   B  |     Wayne | 2     |2019/01/02|
    +------+-----------+=======+==========+

我看到很多脚本来排序和创建 row_number 来执行此操作，但它们无法处理预先存在的顺序未更改的情况。谢谢。

Answer 1

嗯。 。 。您可以使用窗口函数来做到这一点:

select t.*,
       coalesce(ordering,
                (seqnum +
                 sum( case when max_ordering <= seqnum then 1 else 0 end ) over (partition by team order by coalesce(ordering, seqnum))
                )
               ) as new_order
from (select t.*, max(ordering) over (partition by team order by coalesce(ordering, seqnum)) as max_ordering
      from (select t.*,
                   (case when ordering is null
                         then row_number() over (partition by team, ordering is null order by createdat)
                    end) as seqnum
            from t
           ) t
     ) t;

Here是一个数据库<> fiddle 。

这个想法是这样的:

• 使用新的排序方式枚举未排序的行 - 这称为 seqnum。
• 现在您有两个重叠的列表。计算现有 order 值的数量，直至每个 seqnum 值。这很棘手。这个想法是计算订单的数量，其值不超过每行的 seqnum。
• 那么最终的排序是 seqnum 加上上一个项目符号中计算的值，或者是原始 order。