MySQL:迭代和更新表中的记录

Question

我有一个包含如下记录的表格:

USER  | STATUS
--------------
user1 | 1
user2 | 1
user1 | 1
user2 | 1
user1 | 0
user2 | 1
user3 | 1

我想添加一个字段 COUNT 并更新表格，以便 COUNT 的值如下:

USER  | STATUS | COUNT
----------------------
user1 | 1      | 1
user2 | 1      | 1
user1 | 1      | 2
user2 | 1      | 2
user1 | 0      | NULL # Don't want to update where STATUS=0
user2 | 1      | 3
user3 | 1      | 1

我对存储过程了解不多，我试过的并没有真正给出我想要的输出。

如果有人能帮助我，我将不胜感激。谢谢!

Answer 1

不需要存储过程。我添加了一个 auto_increment 列，否则它将是不确定的。

示例数据:

CREATE TABLE t
    (id int auto_increment primary key, `USER` varchar(5), `STATUS` int)
;

INSERT INTO t
    (`USER`, `STATUS`)
VALUES
    ('user1', 1),
    ('user2', 1),
    ('user1', 1),
    ('user2', 1),
    ('user1', 0),
    ('user2', 1),
    ('user3', 1)
;

ALTER TABLE t ADD COLUMN COUNT int default null;

查询:

UPDATE t
INNER JOIN (
  SELECT
  id,
`USER`, `STATUS`,
CASE WHEN (@my_count := IF(@prevUser != USER, 1, @my_count + 1)) IS NULL THEN NULL
WHEN (@prevUser := USER) IS NULL THEN NULL
ELSE @my_count END AS `COUNT`
FROM
t,
(SELECT @my_count := 0, @prevUser := NULL) init
WHERE
STATUS != 0
ORDER BY USER, id
  ) sq ON sq.id = t.id
SET t.COUNT = sq.COUNT;

结果:

| ID |  USER | STATUS |  COUNT |
|----|-------|--------|--------|
|  1 | user1 |      1 |      1 |
|  2 | user2 |      1 |      1 |
|  3 | user1 |      1 |      2 |
|  4 | user2 |      1 |      2 |
|  5 | user1 |      0 | (null) |
|  6 | user2 |      1 |      3 |
|  7 | user3 |      1 |      1 |

• 看到它在 sqlfiddle 中实时运行