mysql获取组内行号,MySQL – 每组包含行号的更新表

样本数据

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作为后备顺序排序.

>我需要一个更新此列的查询.

解决方法:

我就是这样做的：

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

标签：mysql,sql

来源： https://codeday.me/bug/20190611/1219643.html