mysql - 根据列中的分组元素对项目进行编号

Question

这是我的 table :

Item1   | Item2     | Item3     | Element   | ItemNumber
==============================================================
rock    | n         | roll      | r         | 1
rock    | n         | roll      | o         | 0
rock    | n         | roll      | c         | 0
rock    | n         | roll      | k         | 1
rock    | n         | roll      | n         | 2
rock    | n         | roll      | r         | 3
rock    | n         | roll      | o         | 0
rock    | n         | roll      | l         | 0
rock    | n         | roll      | l         | 3
a       | tiny      | rock      | a         | 1
a       | tiny      | rock      | t         | 2
a       | tiny      | rock      | i         | 0
a       | tiny      | rock      | n         | 0
a       | tiny      | rock      | y         | 2
a       | tiny      | rock      | r         | 3
a       | tiny      | rock      | o         | 0
a       | tiny      | rock      | c         | 0
a       | tiny      | rock      | k         | 3

我想更新ItemNumber 列，以使所有元素根据Element 列中的值与其所属的项目编号相对应。基本上，我只想将正确的缺失值添加到 ItemNumber 中，当前值为 0。我的表中有超过 600.000 行，其中元素数量各不相同。

决赛 table 应如下所示:

Item1   | Item2     | Item3     | Element   | ItemNumber
==============================================================
rock    | n         | roll      | r         | 1
rock    | n         | roll      | o         | 1
rock    | n         | roll      | c         | 1
rock    | n         | roll      | k         | 1
rock    | n         | roll      | n         | 2
rock    | n         | roll      | r         | 3
rock    | n         | roll      | o         | 3
rock    | n         | roll      | l         | 3
rock    | n         | roll      | l         | 3
a       | tiny      | rock      | a         | 1
a       | tiny      | rock      | t         | 2
a       | tiny      | rock      | i         | 2
a       | tiny      | rock      | n         | 2
a       | tiny      | rock      | y         | 2
a       | tiny      | rock      | r         | 3
a       | tiny      | rock      | o         | 3
a       | tiny      | rock      | c         | 3
a       | tiny      | rock      | k         | 3

如何使用 MySQL 做到这一点？

Answer 1

您需要的是一列，它唯一标识每一行。拥有这样一个列(也称为主键)总是一个好主意。

另一列的用途是确定行的排序顺序。换句话说，当您选择包含元素 r、o、l 和 l 的行时，该列应列在包含 r、o、c 和 k 的行之后。在一个可能的解决方案中，我为此使用了 auto_increment 列。

CREATE TABLE t
    (id int auto_increment primary key, `Item1` varchar(4), `Item2` varchar(4), `Item3` varchar(4), `Element` varchar(1), `ItemNumber` int)
;

INSERT INTO t
    (`Item1`, `Item2`, `Item3`, `Element`, `ItemNumber`)
VALUES
    ('rock', 'n', 'roll', 'r', 1),
    ('rock', 'n', 'roll', 'o', 0),
    ('rock', 'n', 'roll', 'c', 0),
    ('rock', 'n', 'roll', 'k', 1),
    ('rock', 'n', 'roll', 'n', 2),
    ('rock', 'n', 'roll', 'r', 3),
    ('rock', 'n', 'roll', 'o', 0),
    ('rock', 'n', 'roll', 'l', 0),
    ('rock', 'n', 'roll', 'l', 3),
    ('a', 'tiny', 'rock', 'a', 1),
    ('a', 'tiny', 'rock', 't', 2),
    ('a', 'tiny', 'rock', 'i', 0),
    ('a', 'tiny', 'rock', 'n', 0),
    ('a', 'tiny', 'rock', 'y', 2),
    ('a', 'tiny', 'rock', 'r', 3),
    ('a', 'tiny', 'rock', 'o', 0),
    ('a', 'tiny', 'rock', 'c', 0),
    ('a', 'tiny', 'rock', 'k', 3)
;

UPDATE t
JOIN (
  SELECT
  t.*
  , @v := IF(ItemNumber != 0, ItemNumber, @v) as new_itemnumber
  FROM
  t
  , (SELECT @v:=0) var_init_subquery
  ORDER BY id
  ) sq USING (id)
SET t.ItemNumber = sq.new_itemnumber;

• 看到它在 sqlfiddle 中实时运行 here