是否有一个查询可以更新“序列号"?跨多个组? [英] Is there a single query that can update a "sequence number" across multiple groups?

问题描述

给出如下表，是否有一种单查询方式可以从中更新表:

Given a table like below, is there a single-query way to update the table from this:

| id | type_id | created_at | sequence |
|----|---------|------------|----------|
|  1 |       1 | 2010-04-26 | NULL     |
|  2 |       1 | 2010-04-27 | NULL     |
|  3 |       2 | 2010-04-28 | NULL     |
|  4 |       3 | 2010-04-28 | NULL     |

为此(请注意，使用created_at进行订购，并且sequence被"type_id"分组"):

To this (note that created_at is used for ordering, and sequence is "grouped" by type_id):

| id | type_id | created_at | sequence |
|----|---------|------------|----------|
|  1 |       1 | 2010-04-26 |        1 |
|  2 |       1 | 2010-04-27 |        2 |
|  3 |       2 | 2010-04-28 |        1 |
|  4 |       3 | 2010-04-28 |        1 |

在使用@变量之前，我已经看过一些代码，如下所示，我认为这可能会起作用:

I've seen some code before that used an @ variable like the following, that I thought might work:

SET @seq = 0;
UPDATE `log` SET `sequence` = @seq := @seq + 1
ORDER BY `created_at`;

但这显然不会将每个type_id的序列重置为1.

But that obviously doesn't reset the sequence to 1 for each type_id.

如果没有单查询方式可以做到这一点，最有效的方法是什么?

If there's no single-query way to do this, what's the most efficient way?

此表中的数据可能会被删除，因此我计划在用户完成编辑以对表重新排序后运行存储过程.

Data in this table may be deleted, so I'm planning to run a stored procedure after the user is done editing to re-sequence the table.

推荐答案

您可以使用另一个存储先前type_id(@type_id)的变量.查询是按type_id排序的，因此，只要type_id发生更改，就必须将序列重新设置为1.

You can use another variable storing the previous type_id (@type_id). The query is ordered by type_id, so whenever there is a change in type_id, sequence has to be reset to 1 again.

Set @seq = 0;
Set @type_id = -1;

Update `log`
Set `sequence` = If(@type_id=(@type_id:=`type_id`), (@seq:=@seq+1), (@seq:=1))
Order By `type_id`, `created_at`;

这篇关于是否有一个查询可以更新“序列号"?跨多个组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！