mysql 使用特定字段的最大日期时间更新表

798qvoo8 于 2023-05-21 发布在 Mysql

关注(0)|答案(1)|浏览(120)

我有一个表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 |
+-----+--------+---------------------+----------------------+

以及表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 |
+----+--------+----------------------+

我需要将最后测量的P_Si从containers_measure放入表boxes中的字段power，并对应number。对于给定的number，来自containers_measure的datetime应该是最大的。我试过这个：

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);

但是由于不可能在update语句中使用having和join，因此会导致错误。
我该如何解决这个问题？

mysql

来源：https://stackoverflow.com/questions/76298341/update-table-with-join-using-maximum-datetime-for-certain-field

1条答案

按热度按时间

a1o7rhls1#

在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;

赞(0）回复(0）举报 2023-05-21

我来回答

mysql 使用特定字段的最大日期时间更新表

1条答案

相关问题

热门标签

最新问答