MYSQL - 更新具有递增值的列，每次满足条件时都会重置

Question

我有这张 table 。

+++++++++++++++++++++++++++++++++ itemid +   name   + group_id ++++++++++++++++++++++++++++++++++   1    +  name 1  +    0     ++   2    +  name 2  +    1     ++   3    +  name 3  +    1     ++   4    +  name 4  +    0     ++   5    +  name 5  +    0     ++   6    +  name 6  +    2     ++   7    +  name 7  +    2     ++   8    +  name 8  +    2     ++   9    +  name 9  +    3     ++   10   +  name 10 +    3     ++   11   +  name 11 +    3     ++   12   +  name 12 +    3     ++   13   +  name 13 +    0     ++   14   +  name 14 +    4     ++   15   +  name 15 +    0     +++++++++++++++++++++++++++++++++

我向该表中添加了一个默认值为 0 的新列 group_order_id，这样组内的项目之间就会有一个顺序。

++++++++++++++++++++++++++++++++++++++++++++++++++ itemid +   name   + group_id + group_order_id +++++++++++++++++++++++++++++++++++++++++++++++++++   1    +  name 1  +    0     +       0        ++   2    +  name 2  +    1     +       0        ++   3    +  name 3  +    1     +       0        ++   4    +  name 4  +    0     +       0        ++   5    +  name 5  +    0     +       0        ++   6    +  name 6  +    2     +       0        ++   7    +  name 7  +    2     +       0        ++   8    +  name 8  +    2     +       0        ++   9    +  name 9  +    3     +       0        ++   10   +  name 10 +    3     +       0        ++   11   +  name 11 +    3     +       0        ++   12   +  name 12 +    3     +       0        ++   13   +  name 13 +    0     +       0        ++   14   +  name 14 +    4     +       0        ++   15   +  name 15 +    0     +       0        ++++++++++++++++++++++++++++++++++++++++++++++++++

我想更新列 group_order_id 以便:

• 如果 group_id=0，group_order_id=0
• 如果 group_id 相同(0 除外)，则每个项目的 group_order_id 的值将从 1-X 递增，如下所示。

++++++++++++++++++++++++++++++++++++++++++++++++++ itemid +   name   + group_id + group_order_id +++++++++++++++++++++++++++++++++++++++++++++++++++   1    +  name 1  +    0     +       0        ++   2    +  name 2  +    1     +       1        ++   3    +  name 3  +    1     +       2        ++   4    +  name 4  +    0     +       0        ++   5    +  name 5  +    0     +       0        ++   6    +  name 6  +    2     +       1        ++   7    +  name 7  +    2     +       2        ++   8    +  name 8  +    2     +       3        ++   9    +  name 9  +    3     +       1        ++   10   +  name 10 +    3     +       2        ++   11   +  name 11 +    3     +       3        ++   12   +  name 12 +    3     +       4        ++   13   +  name 13 +    0     +       0        ++   14   +  name 14 +    4     +       1        ++   15   +  name 15 +    0     +       0        ++++++++++++++++++++++++++++++++++++++++++++++++++

有办法做到这一点吗？

Answer 1

您需要使用排名查询，然后将其用作 UPDATE 语句中的子查询，如下所示:

排名查询:

SELECT IF(@prev = group_id, @s:=@s+1, @s:=1) AS `group_order_id`, itemid, @prev:=group_id AS group_id
FROM table1, (SELECT @s:= 1, @prev:= 0) s
WHERE group_id <> 0
ORDER BY group_id

更新查询:

UPDATE table1, (
  SELECT IF(@prev = group_id, @s:=@s+1, @s:=1) AS `group_order_id`, itemid, @prev:=group_id AS group_id
  FROM table1, (SELECT @s:= 1, @prev:= 0) s
  WHERE group_id <> 0
  ORDER BY group_id
) AS t
SET table1.group_order_id = t.group_order_id
WHERE table1.itemid = t.itemid

工作演示:http://sqlfiddle.com/#!2/08259/1/0