MySQL:根据不同列的变化对列进行顺序编号

Question

如果我有一个包含以下列和值的表，按 parent_id 排序:

id    parent_id    line_no
--    ---------    -------
1     2            
2     2
3     2
4     3
5     4
6     4

我想用一个序列号填充 line_no，每当 parent_id 的值发生变化时，该序列号从 1 开始:

id    parent_id    line_no
--    ---------    -------
1     2            1
2     2            2
3     2            3
4     3            1
5     4            1
6     4            2

查询或存储过程是什么样的？

注意:我应该指出我只需要这样做一次。我的 PHP 代码中有一个新函数，它会在每次添加新记录时自动创建 line_no。我只需要更新已经存在的记录。

Answer 1

大多数版本的 MySQL 不支持 row_number()。因此，您可以使用变量来做到这一点。但是你必须非常小心。 MySQL 不保证 select 中变量的求值顺序，因此不应在不同的表达式中为变量赋值和引用。

所以:

select t.*,
       (@rn := if(@p = parent_id, @rn + 1,
                  if(@p := parent_id, 1, 1)
                 )
       ) as line_no
from (select t.* from t order by id) t cross join
     (select @p := 0, @rn := 0) params;

对表进行排序的子查询可能不是必需的。在 5.7 版左右的某个地方，这在使用变量时变得很有必要。

编辑:

用变量更新很有趣。在这种情况下，我将只使用上面的子查询:

update t join
       (select t.*,
               (@rn := if(@p = parent_id, @rn + 1,
                          if(@p := parent_id, 1, 1)
                         )
               ) as new_line_no
        from (select t.* from t order by id) t cross join
             (select @p := 0, @rn := 0) params
       ) tt
       on t.id = tt.id
    set t.line_no = tt.new_line_no;