我想使用同一组的 MIN(id) 值更新open
组 ( exchange, base_currency, quote_currency, DATE(created_at)
)的 MAX(id)值。
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),但不确定如何使用这些值来更新open
组的 MAX(id)的值。
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句