MySQL UPDATE值基于其顺序

I want to give a value to my row based on its id position but only for a certain timestamp.

我想基于其id位置给我的行赋值,但仅限于某个时间戳。

Let's say I got a tbl1 with id, tbl2_id value timestamp and a tbl2 with id and timestamp. My two tables are related by tbl2.id = tbl1.tbl2_id.

假设我有一个带有id,tbl2_id值时间戳的tbl1和一个带有id和timestamp的tbl2。我的两个表与tbl2.id = tbl1.tbl2_id相关。

First, I want to

首先,我想

SELECT * 
FROM tbl1
WHERE tbl1.timestamp > tbl2.timestamp 
GROUP BY tbl2_id

For example,if this return me 100 rows. I want to SET tbl1.value = 1 for row 1-10, based on the tbl1.id ascending order. Then, SET tbl1.value = 2 row 11-20 and so on...

例如,如果这返回100行。我想根据tbl1.id升序为行1-10设置tbl1.value = 1。然后,SET tbl1.value = 2行11-20等等......

I believe that I can do this last part using CASE. However I'm not sure how to put all this in one UPDATE query.

我相信我可以使用CASE做最后一部分。但是我不确定如何将所有这些放在一个UPDATE查询中。

2 个解决方案

#1

I am guessing you have a typo and really meant SET tbl1.value = 2 row 11-20 and so on.

我猜你有一个错字,真的意味着SET tbl1.value = 2行11-20等等。

This is not tested but how about the following:

这未经过测试,但以下情况如何:

SET @rank=0;
UPDATE tbl1 AS mt1
  INNER JOIN 
  (
    SELECT @rank := @rank + 1 AS RowValue, st1.id
    FROM tbl1 AS st1
      INNER JOIN tbl2 AS st2 
      ON st1.Whatever = st2.Whatever
    WHERE st1.timestamp > st2.timestamp 
    GROUP BY st2.tbl2_id
  ) AS TempQ
  ON TempQ.id = mt1.id
SET mt1.value = 
  CAST(((TempQ.RowValue / 10) AS UNSIGNED) + 
  CASE WHEN TempQ.RowValue MOD 10 = 0 THEN 0 ELSE 1 END

In the above query the following occurs:

在上面的查询中会发生以下情况:

tbl1.value = 1 if RowValue is less then 10

如果RowValue小于10,则tbl1.value = 1

tbl1.value = 2 if RowValue is between 10 and 20

如果RowValue介于10和20之间,则tbl1.value = 2

tbl1.value = 3 if RowValue is between 20 and 30

如果RowValue介于20和30之间,则tbl1.value = 3

etc.

I do not know how tbl1 links to tbl2. So you will have to change st1.Whatever = st2.Whatever to whatever is correct.

我不知道tbl1如何链接到tbl2。所以你必须改变st1.Whatever = st2.Whatever what what what correct。

#2

You can simply update across a join like this:

您可以简单地更新这样的连接:

UPDATE tlb1
INNER JOIN tbl2
  ON tbl1.tbl2_id = tbl2.id
SET tbl1.value = 1
WHERE tbl21.timestamp > tbl2.timestamp

#1