mysql - 在聚合函数上使用选择查询插入

Question

引用以下主题: Can you use aggregate values within ON DUPLICATE KEY

我尝试复制该方法以及许多其他方法在 ma20 上插入值，但没有任何效果，并且不断收到以下错误:错误代码:1054。“字段列表”中存在未知列“histClose”

我做错了什么以及如何才能完成这项工作？

insert into moving_average (ma_symbol, ma_date, ma20)
select 'A', max(histDate) as maxHistDate, avg(histClose) as histClose
from
   (select histDate, histClose
   from historical_data
   where symbol like 'A'
   order by histDate asc
   limit 20) temp
   on duplicate key update ma20 = values(histClose);

Answer 1

values 关键字采用表中列的名称。 。 。这是将进入该列的值:

insert into moving_average (ma_symbol, ma_date, ma20)
    select 'A', max(histDate) as maxHistDate, avg(histClose) as histClose
    from (select histDate, histClose
          from historical_data
          where symbol like 'A'
          order by histDate asc
          limit 20
         ) temp
    on duplicate key update ma20 = values(ma20);