按相同值的字段分组的序列号的MySQL更新列

Question

我想使用一个值相同的字段作为参考，在我的MySQL表的一列中进行大规模的更新。update是一个在分组时重复的数字序列。

MySQL版本：

5.6.41-84.1

MySQL表：

sis_coautoria

列和值

id_auto_coautoria | id_coautor | hierarquia_coautoria | id_trabalho

1, 1, null, 123
2, 43, null, 123
3, 2, null, 123
4, 2, null, 100
5, 1, null, 675
6, 50, null, 100

我想更新hierarquia_coautoria列，使用id_trabalho列作为引用，使用id_auto_coautoria作为ORDER。预期的结果如下：

id_auto_coautoria | id_coautor | hierarquia_coautoria | id_trabalho

1, 1, 1, 123
2, 43, 2, 123
3, 2, 3, 123
4, 2, 1, 100
6, 50, 2, 100
5, 1, 1, 675

id_trabalho = 123的行用1、2、3进行更新；id_trabalho = 100用1、2更新，依此类推。

*如果需要的话，还有另一个表以id_trabalho列作为参考。

其他MySQL表

sis_trabalhos

id_auto_trabalho | id_trabalho

1, 123
2, 100
3, 675

，所以，我尝试过这样的方法：

SET @rank:=0;
UPDATE sis_coautoria
INNER JOIN sis_trabalhos ON sis_coautoria.id_trabalho = sis_trabalhos.id_trabalho
SET sis_coautoria.hierarquia_coautoria=@rank:=@rank+1; 

结果：

id_auto_coautoria | id_coautor | hierarquia_coautoria | id_trabalho

1, 1, 1, 123
2, 43, 2, 123
3, 2, 3, 123
4, 2, 4, 100
5, 1, 5, 675
6, 50, 6, 100
...

结果是一个忽略id_trabalho条件为相同值的数字序列。

PS.：如何显示这样友好的视图的MySQL表？

​

Answer 1

我理解的是，您需要一组id_trabalho列的行号。因为Mysql 5没有CTE，所以可以使用子查询来执行相同的操作。

    update sis_coautoria a
join 
(SELECT 
    @row_number:=CASE
        WHEN @id_trabalho = id_trabalho THEN @row_number + 1
        ELSE 1
    END AS rn,
    @id_trabalho:=id_trabalho as id_trabalho,
    id_auto_coautoria
FROM
    sis_coautoria
ORDER BY id_auto_coautoria)
cte on a.id_auto_coautoria=cte.id_auto_coautoria
set a.hierarquia_coautoria=cte.rn

注意:我没有在编辑器上运行这个查询。

Answer 2

这个答案是给MySQL 5的。MySQL 8(或更高版本)还有其他方法来获得相同的结果。

@BlackSwan的贡献帮助我找到了问题的答案。谢谢!

我的MySQL服务器版本是5.6，不允许number_row()函数或CTE过程。因此，该解决方案模拟了带有子查询的MySQL ROW_NUMBER()函数，以填充用于更新所需列MySQL的新列"sequencia“

这里是获取所需结果的代码：

update sis_coautoria a
join 
(SELECT 
    @row_number:=CASE
        WHEN @trabalhoID = id_trabalho THEN @row_number + 1
        ELSE 1
    END AS sequencia,
    @trabalhoID:=id_trabalho as trabalho_id,
    id_coautor_auto,
    id_usuario
FROM
    sis_coautoria,(SELECT @trabalhoID:=0,@row_number:=0) as t
ORDER BY id_trabalho ASC)
cte on a.id_coautor_auto=cte.id_coautor_auto
set a.hierarquia_coautoria=cte.sequencia

代码解释：

我们使用一个变量(@row_number)来存储这个条件下的行计数号:当trabalho_id有相同的值时，一个新的行号将以数字顺序的方式添加到后缀列中。

@row_number:=CASE
            WHEN @trabalhoID = id_trabalho THEN @row_number + 1
            ELSE 1
        END AS sequencia

按照顺序，我们使用一个条件来连接引用的列，并用存储在后缀列中的值更新所需的列(hierarquia_coautoria)。

cte on a.id_coautor_auto=cte.id_coautor_auto
    set a.hierarquia_coautoria=cte.sequencia 

其他信息：

链接到数字()仿真教程

链接到MySQL 8中的NUMBER()函数

关于MySQL 8+