gpt4 book ai didi

MySQL更新查询最近1年的平均值

转载 作者:行者123 更新时间:2023-11-29 21:25:20 25 4
gpt4 key购买 nike

我正在尝试通过取过去 1 年值的平均值来更新另一个表中的值。我在下面编写的查询肯定是错误的,因为 AsOfDate 尚未包含在表 b 的查询中,但它表明了我正在尝试执行的操作。提前致谢。

update MT_1YR_AVG_EXGRATE a
join (select avg(ExchangeRate) as ExchangeRate, CurrencyId from Currency_Exchange_Rate_Data
where BaseCurrencyId = "USD" group by CurrencyId) b
on b.CurrencyId = a.CurrencyId and b.AsOfDate <= a.AsOfDate and b.AsOfDate > date_sub(a.AsOfDate,interval 1 year)
set a.ExchangeRate = b.ExchangeRate
where a.ExchangeRate = null;

最佳答案

已解决:

update MT_1YR_AVG_EXGRATE c join (
select b.CurrencyId, b.AsOfDate, avg(a.ExchangeRate) as ExchangeRate from Currency_Exchange_Rate_Data a, MT_1YR_AVG_EXGRATE b
where a.BaseCurrencyId = "USD" and a.CurrencyId = b.CurrencyId and b.ExchangeRate is NULL
and a.AsOfDate <= b.AsOfDate and a.AsOfDate > date_sub(b.AsOfDate,interval 1 year)
group by b.CurrencyId, b.AsOfDate) d
on d.CurrencyId = c.CurrencyId and d.AsOfDate = c.AsOfDate
set c.ExchangeRate = cast(d.ExchangeRate as decimal(19,5))
where c.ExchangeRate is NULL;

关于MySQL更新查询最近1年的平均值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35535814/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com