mysql - sql - 使用mysql脚本,如何以跳过现有顺序的方式生成行号

62 2

我有一个表


+======+===========+=======+==========+


| 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基于以下规则设置顺序

  • 基于team
  • 由createdAt排序
  • 订单不应在team内重复,

对于上面的示例,我期望的是


 +======+===========+=======+==========+


 | 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来执行这个操作,但是它们无法处理现有顺序不变的情况。 谢谢

时间: 原作者:

60 2

你可以用window函数做到这一点:


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;



  • 用新的排序--枚举无序行这称为seqnum
  • 现在你有两个列表,有重叠,计算现有order值的数目,直到每个seqnum值,这很棘手。 这个方法的目的是计算每行的seqnum值以下的订单数。
  • 那么最后的排序是seqnum加上前一个项目符号计算的值,或者是原始排序。

原作者:
...