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

我正在尝试通过获取最近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 - MySQL更新查询的最近1年平均值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35535814/