mysql - mysql使用同一组的MIN值更新组的MAX值，多行

我想使用同一组的MIN（id）的值更新组（open）的MAX（id）的exchange, base_currency, quote_currency, DATE(created_at)值。

id last open exchange base_curr quote_curr created_at

6  1.11 0.00 ex1      usd       yen        2018-07-29 03:00:00 --> update open with 1.09 (MIN(last) of group)
5  1.09 0.00 ex1      usd       yen        2018-07-29 02:00:00
4  1.14 0.00 ex1      usd       yen        2018-07-29 01:00:00

3  0.49 0.00 ex2      yen       won        2018-07-29 03:00:00 --> update open with 0.49 (MIN(last) of group)
2  0.51 0.00 ex2      yen       won        2018-07-29 02:00:00
1  0.50 0.00 ex2      yen       won        2018-07-29 01:00:00

我知道如何获取组的所有MIN（id），但不确定如何使用这些值更新组的MAX（id）的open值。

MAX（id）或MAX（created_at）将使我获得组的最新行。

SELECT MIN(id) as min_id, last
FROM tickers
WHERE DATE(created_at) = '2018-07-29'
GROUP BY exchange, base_currency, quote_currency, DATE(created_at)

最佳答案

我想你要：

update tickers t join
       (select exchange, base_curr, quote_curr, date(created_at) as created_at_date,
               max(id) as maxid, min(last) as minlast
        from tickers t2
        group by exchange, base_curr, quote_curr, date(created_at)
       ) tt
       on tt.maxid = t.id
    set t.open = tt.minlast;

关于mysql - mysql使用同一组的MIN值更新组的MAX值，多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51624022/