MySQL - 用每组的行号更新表

Question

示例数据

id  |  order_id  |  instalment_num  | date_due
---------------------------------------------------------
1   |  10000     |  1               | 2010-07-09 00:00:00
2   |  10000     |  1               | 2010-09-06 11:39:56
3   |  10001     |  1               | 2014-04-25 15:46:52
4   |  10002     |  1               | 2010-01-11 00:00:00
5   |  10003     |  1               | 2010-01-04 00:00:00
6   |  10003     |  1               | 2016-05-31 00:00:00
7   |  10003     |  1               | 2010-01-08 00:00:00
8   |  10003     |  1               | 2010-01-06 09:06:26
9   |  10004     |  1               | 2010-01-11 11:25:07
10  |  10004     |  1               | 2010-01-12 07:06:42

期望的结果

id  |  order_id  |  instalment_num  | date_due
---------------------------------------------------------
1   |  10000     |  1               | 2010-07-09 00:00:00
2   |  10000     |  2               | 2010-09-06 11:39:56
3   |  10001     |  1               | 2014-04-25 15:46:52
4   |  10002     |  1               | 2010-01-11 00:00:00
5   |  10003     |  1               | 2010-01-04 00:00:00
8   |  10003     |  2               | 2010-01-06 09:06:26
7   |  10003     |  3               | 2010-01-08 00:00:00
6   |  10003     |  4               | 2016-05-31 00:00:00
9   |  10004     |  1               | 2010-01-11 11:25:07
10  |  10004     |  2               | 2010-01-12 07:06:42

如您所见，我有一个 instalment_num 列，该列应显示属于 order_id 的每一行的编号/索引，由 date_due ASC 确定, id ASC顺序。

如何像这样更新 instalment_num 列？

附加说明

• date_due 列不是唯一的，可能有许多具有完全相同时间戳的 id 或 order_id。

• 如果属于同一 order_id 的两行的时间戳相同，则应按 id 对它们进行排序作为后备。

• 我需要一个查询来更新此列。

Answer 1

我会这样做:

SELECT a.id,
    a.order_id,
    COUNT(b.id)+1 AS instalment_num,
    a.date_due
FROM sample_data a
    LEFT JOIN sample_data b ON a.order_id=b.order_id AND (a.date_due>b.date_due OR (a.date_due=b.date_due AND a.id>b.id))
GROUP BY a.id, a.order_id, a.date_due
ORDER BY a.order_id, a.date_due, a.id

更新版本尝试:

UPDATE sample_data
    LEFT JOIN (SELECT a.id,
    COUNT(b.id)+1 AS instalment_num
FROM sample_data a
    JOIN sample_data b ON a.order_id=b.order_id AND (a.date_due>b.date_due OR (a.date_due=b.date_due AND a.id>b.id))
GROUP BY a.id) c ON c.id=sample_data.id
SET sample_data.instalment_num=c.instalment_num