使用最大日期时间更新表格并进行连接以更新特定字段

huangapple go评论69阅读模式
英文:

Update table with join using maximum datetime for certain field

问题

你可以尝试使用子查询来解决这个问题。以下是一个可能的解决方案:

UPDATE boxes
JOIN (
    SELECT number, MAX(meas_date) AS max_meas_date
    FROM containers_measure
    GROUP BY number
) AS subquery
ON boxes.number = subquery.number
JOIN containers_measure
ON boxes.number = containers_measure.number AND subquery.max_meas_date = containers_measure.meas_date
SET boxes.power = containers_measure.P_Si;

这个SQL查询会首先创建一个子查询,找到每个number对应的最大meas_date。然后,它将这个子查询与boxes表和containers_measure表进行连接,确保number匹配并且meas_date也匹配。最后,它会将containers_measure表中找到的最后一次测量的P_Si值赋给boxes表的power字段。

请注意,这个查询假设每个numbercontainers_measure表中都有至少一条对应的记录。如果有可能存在没有匹配记录的number,则需要进一步处理。

英文:

I have a table containers_measure:

+-----+--------+---------------------+----------------------+
| id  | number | meas_date           | P_Si                 |
+-----+--------+---------------------+----------------------+
| 549 |     22 | 2023-05-21 11:34:05 | 0.010040000081062317 |
| 550 |     15 | 2023-05-21 11:34:05 | 0.009720000438392162 |
| 551 |     18 | 2023-05-21 11:34:05 | 0.009859999641776085 |
| 552 |     16 | 2023-05-21 11:34:05 | 0.009850000031292439 |
| 553 |     31 | 2023-05-21 11:34:05 |  0.00965999998152256 |
| 554 |     23 | 2023-05-21 11:34:05 | 0.009960000403225422 |
| 555 |      6 | 2023-05-21 11:34:05 |   0.0102300001308322 |
| 556 |      5 | 2023-05-21 11:34:05 | 0.010200000368058681 |
| 557 |      4 | 2023-05-21 11:34:05 | 0.010169999673962593 |
| 558 |     13 | 2023-05-21 11:34:05 | 0.010099999606609344 |
| 559 |     36 | 2023-05-21 11:34:05 | 0.009829999879002571 |
| 560 |      3 | 2023-05-21 11:34:05 | 0.010099999606609344 |
| 561 |      2 | 2023-05-21 11:34:05 | 0.010180000215768814 |
| 562 |      1 | 2023-05-21 11:34:05 |   0.0102399997413158 |
| 563 |     32 | 2023-05-21 11:34:05 | 0.010230000130832195 |
| 564 |     30 | 2023-05-21 11:34:05 | 0.010250000283122063 |
| 565 |     27 | 2023-05-21 11:34:05 | 0.009960000403225422 |
| 566 |      8 | 2023-05-21 11:34:05 | 0.010040000081062317 |
| 567 |      7 | 2023-05-21 11:34:05 | 0.010049999691545963 |
| 568 |     33 | 2023-05-21 11:34:05 | 0.010049999691545963 |
| 569 |     10 | 2023-05-21 11:34:05 |   0.0101500004529953 |
| 570 |     26 | 2023-05-21 11:34:05 | 0.010180000215768814 |
| 571 |     25 | 2023-05-21 11:34:05 | 0.010099999606609344 |
| 572 |     24 | 2023-05-21 11:34:05 | 0.010040000081062317 |
| 573 |     17 | 2023-05-21 11:34:05 | 0.009650000371038914 |
| 574 |     14 | 2023-05-21 11:34:05 |  0.00977999996393919 |
| 575 |     12 | 2023-05-21 11:34:05 | 0.009879999794065952 |
| 576 |     11 | 2023-05-21 11:34:05 |  0.00953999999910593 |
| 577 |     21 | 2023-05-21 11:34:05 | 0.009700000286102295 |
| 578 |      9 | 2023-05-21 11:34:05 | 0.009850000031292439 |
| 579 |     28 | 2023-05-21 11:34:05 |  0.00977999996393919 |
| 580 |     19 | 2023-05-21 11:34:05 | 0.009809999726712704 |
| 612 |     22 | 2023-05-21 11:38:11 | 0.010040000081062317 |
| 613 |     15 | 2023-05-21 11:38:11 | 0.009720000438392162 |
| 614 |     18 | 2023-05-21 11:38:11 | 0.009859999641776085 |
| 615 |     16 | 2023-05-21 11:38:11 | 0.009850000031292439 |
| 616 |     31 | 2023-05-21 11:38:11 |  0.00965999998152256 |
| 617 |     23 | 2023-05-21 11:38:11 | 0.009960000403225422 |
| 618 |      6 | 2023-05-21 11:38:11 |   0.0102300001308322 |
| 619 |      5 | 2023-05-21 11:38:11 | 0.010200000368058681 |
| 620 |      4 | 2023-05-21 11:38:11 | 0.010169999673962593 |
| 621 |     13 | 2023-05-21 11:38:11 | 0.010099999606609344 |
| 622 |     36 | 2023-05-21 11:38:11 | 0.009829999879002571 |
| 623 |      3 | 2023-05-21 11:38:11 | 0.010099999606609344 |
| 624 |      2 | 2023-05-21 11:38:11 | 0.010180000215768814 |
| 625 |      1 | 2023-05-21 11:38:11 |   0.0102399997413158 |
| 626 |     32 | 2023-05-21 11:38:11 | 0.010230000130832195 |
| 627 |     30 | 2023-05-21 11:38:11 | 0.010250000283122063 |
| 628 |     27 | 2023-05-21 11:38:11 | 0.009960000403225422 |
| 629 |      8 | 2023-05-21 11:38:11 | 0.010040000081062317 |
| 630 |      7 | 2023-05-21 11:38:11 | 0.010049999691545963 |
| 631 |     33 | 2023-05-21 11:38:11 | 0.010049999691545963 |
| 632 |     10 | 2023-05-21 11:38:11 |   0.0101500004529953 |
| 633 |     26 | 2023-05-21 11:38:11 | 0.010180000215768814 |
| 634 |     25 | 2023-05-21 11:38:11 | 0.010099999606609344 |
| 635 |     24 | 2023-05-21 11:38:11 | 0.010040000081062317 |
| 636 |     17 | 2023-05-21 11:38:11 | 0.009650000371038914 |
| 637 |     14 | 2023-05-21 11:38:11 |  0.00977999996393919 |
| 638 |     12 | 2023-05-21 11:38:11 | 0.009879999794065952 |
| 639 |     11 | 2023-05-21 11:38:11 |  0.00953999999910593 |
| 640 |     21 | 2023-05-21 11:38:11 | 0.009700000286102295 |
| 641 |      9 | 2023-05-21 11:38:11 | 0.009850000031292439 |
| 642 |     28 | 2023-05-21 11:38:11 |  0.00977999996393919 |
| 643 |     19 | 2023-05-21 11:38:11 | 0.009809999726712704 |
+-----+--------+---------------------+----------------------+

and a table boxes:

+----+--------+----------------------+
| id | number | power                |
+----+--------+----------------------+
|  8 |     22 | 0.010040000081062317 |
|  9 |     15 | 0.009720000438392162 |
| 12 |     18 | 0.009859999641776085 |
| 13 |     16 | 0.009850000031292439 |
| 14 |     31 |  0.00965999998152256 |
| 16 |     23 | 0.009960000403225422 |
| 17 |      6 |   0.0102300001308322 |
| 18 |      5 | 0.010200000368058681 |
| 19 |      4 | 0.010169999673962593 |
| 20 |     13 | 0.010099999606609344 |
| 21 |     36 | 0.009829999879002571 |
| 22 |      3 | 0.010099999606609344 |
| 23 |      2 | 0.010180000215768814 |
| 24 |      1 |   0.0102399997413158 |
| 25 |     32 | 0.010230000130832195 |
| 26 |     30 | 0.010250000283122063 |
| 27 |     27 | 0.009960000403225422 |
| 29 |      8 | 0.010040000081062317 |
| 30 |      7 | 0.010049999691545963 |
| 31 |     33 | 0.010049999691545963 |
| 32 |     10 |   0.0101500004529953 |
| 33 |     26 | 0.010180000215768814 |
| 34 |     25 | 0.010099999606609344 |
| 35 |     24 | 0.010040000081062317 |
| 36 |     17 | 0.009650000371038914 |
| 37 |     14 |  0.00977999996393919 |
| 38 |     12 | 0.009879999794065952 |
| 39 |     11 |  0.00953999999910593 |
| 42 |     21 | 0.009700000286102295 |
| 53 |      9 | 0.009850000031292439 |
| 54 |     28 |  0.00977999996393919 |
| 55 |     19 | 0.009809999726712704 |
+----+--------+----------------------+

I need to put the last measured P_Si from containers_measure to the field power in the table boxes with corresponding number. The datetime from containers_measure should be maximal for the given number. I tried this:

update boxes join containers_measure using(number) 
    set boxes.power=containers_measure.P_Si  
    group by containers_measure.number 
    having meas_date=max(meas_date);

but since it is impossible to use having in an update statement with join, it causes an error.

How can I solve this problem?

答案1

得分: 1

在MySQL 8+中,我们可以使用ROW_NUMBER和内连接来表示您的更新操作:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY number ORDER BY meas_date DESC) rn
    FROM containers_measure
)

UPDATE boxes b
INNER JOIN cte t ON t.number = b.number
SET b.power = t.P_Si  
WHERE rn = 1;

在早期版本的MySQL中,我们必须采用纯连接的方式:

UPDATE boxes b
INNER JOIN containers_measure c1
    ON c1.number = b.number
INNER JOIN
(
    SELECT number, MAX(meas_date) AS max_meas_date
    FROM containers_measure
    GROUP BY number
) c2
    ON c2.number = c1.number AND
       c2.max_meas_date = c1.meas_date
SET
    b.power = c1.P_Si;
英文:

On MySQL 8+, we can express your update as an inner join with the help of ROW_NUMBER:

<!-- language: sql -->

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY number ORDER BY meas_date DESC) rn
    FROM containers_measure
)

UPDATE boxes b
INNER JOIN cte t ON t.number = b.number
SET b.power = t.P_Si  
WHERE rn = 1;

On earlier versions of MySQL, we would have to go with a pure join approach:

<!-- language: sql -->

UPDATE boxes b
INNER JOIN containers_measure c1
    ON c1.number = b.number
INNER JOIN
(
    SELECT number, MAX(meas_date) AS max_meas_date
    FROM containers_measure
    GROUP BY number
) c2
    ON c2.number = c1.number AND
       c2.max_meas_date = c1.meas_date
SET
    b.power = c1.P_Si;

huangapple
  • 本文由 发表于 2023年5月21日 12:49:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76298341.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定