mysql - 如何在MySql中用条件更新同一行？

Question

这是数据

key ID |队列|支付
65            1          0
60            2          0
58            3          1
57            4          1
55            5          0
54            6          0
53            7          1
50            8          1

如果像这样的数据，我需要一个 MySql 查询将其更新为如下数据表，仅更新队列列。

key ID |队列|支付
65            0          0
60            0          0
58            1          1
57            2          1
55            0          0
54            0          0
53            3          1
50            4          1

我已经尝试过

更新表1 设置 Queue = case 当 Pay = 0 时 Queue=Queue-1 else Queue

但是队列号看起来不是这样的。

请提出建议。预先感谢您

Answer 1

我们可以尝试加入一个计算序列的子查询:

UPDATE yourTable k1
INNER JOIN
(
    SELECT KeyID, rn
    FROM
    (
        SELECT t1.KeyID,
            (SELECT SUM(t2.Pay = 1) FROM yourTable t2
             WHERE t2.KeyID >= t1.KeyID) rn
        FROM yourTable t1
    ) t
) k2
    ON k1.KeyID = k2.KeyID
SET
    Queue = CASE WHEN Pay = 0 THEN 0 ELSE k2.rn END;

要了解上述逻辑是如何工作的，中间连接表如下所示:

KeyID | Queue | Pay | rn
65    | 1     | 0   | 0
60    | 2     | 0   | 0
58    | 3     | 1   | 1
57    | 4     | 1   | 2
55    | 5     | 0   | 2
54    | 6     | 0   | 2
53    | 7     | 1   | 3
50    | 8     | 1   | 4

即最里面的相关子查询通过统计Pay为1的次数来生成队列序列。

请注意，如果您使用 MySQL 8+，则使用分析函数的查询要简单得多:

UPDATE yourTable
SET Queue = CASE WHEN Pay = 0
                 THEN 0
                 ELSE SUM(Pay = 1) OVER (ORDER BY KeyID DESC) END;