重构移动平均选择语句中的更新语句

Question

我有一个工作选择语句，可以选择1Day_rank的移动平均值

SELECT
 *, 
 (SELECT AVG(1day_rank)
  FROM keyword_rank T2
  WHERE (
           SELECT COUNT(*)
           FROM keyword_rank T3
           WHERE timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=t2.keyword_id 
      ) BETWEEN 1 AND 7 and T2.keyword_id=T1.keyword_id and (T2.timestamp > T1.timestamp - interval 7 day)
 ) average
FROM keyword_rank T1 where T1.keyword_id=86;

我想将此语句重构为更新语句，该语句将将结果更新为7day_rank。但My_sql不允许这样做："错误代码：1093：您不能指定目标表't1'以进行从子句中更新"

update keyword_rank T1 set T1.7day_rank=
 (
 SELECT
      AVG(1day_rank)
 FROM
      (select * from keyword_rank sub where sub.keyword_id=86) as T2
 WHERE
      (
           SELECT
                COUNT(*)
           FROM
                keyword_rank T3
           WHERE
                timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=86 
      ) BETWEEN 1 AND 7 and (T2.timestamp > T1.timestamp - interval 7 day)
 )
  where T1.keyword_id=86;

因此，我尝试使用内部加入来重构此语句，但这会创建另一个错误"错误代码：1054，未知列't1.timestamp'where子句'"

update keyword_rank T1 inner join (
  select AVG(1day_rank) average, timestamp from keyword_rank T2
  where (
           SELECT COUNT(*)
           FROM keyword_rank T3
           WHERE timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=86 
      ) BETWEEN 1 AND 7 and T2.keyword_id=86 and (T2.timestamp >   T1.timestamp - interval 7 day)
 ) as TX set T1.7day_rank=TX.average;

Answer 1

我能够通过将整个工作选择并将其放入内部连接来修复查询。

UPDATE keyword_rank as U
        INNER JOIN
    (
        SELECT
             T1.keyword_id, T1.timestamp, 
             (SELECT AVG(1day_rank)
             FROM keyword_rank T2
             WHERE (
                       SELECT COUNT(*)
                       FROM keyword_rank T3
                       WHERE timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=T2.keyword_id 
                  ) BETWEEN 1 AND 7 and T2.keyword_id=t1.keyword_id and (T2.timestamp > T1.timestamp - interval 7 day) 
             ) as average
        FROM keyword_rank T1 where T1.keyword_id=86
    ) T on U.keyword_id=T.keyword_id and U.timestamp=T.timestamp
SET 
    U.7day_rank = average;