用于平均缺失日期的 MySQL

Question

我希望更新 date 列为 NULL 的行，方法是对该行之前最近的 id 和之后最近的 id 的日期进行平均。

在下面的数据中，它会将 ID 为 26 的行更新为 2011-10-19

什么 MySQL 语句可以完成这个？

---

附加说明:

数据看起来像这样:

id     date           title
--------------------------------------------
12     2011-09-01     Example One
23     2011-10-02     Example Two
26     NULL           Example Three
27     2011-11-05     Example Four
29     2012-01-05     Example Five
37     NULL           Example Six
38     2012-02-03     Example Seven
--------------------------------------------

我不想使用程序。

到目前为止我已经...

UPDATE `table`
    SET `date`=
         (AVG(
              (SELECT `date` FROM `table` WHERE `id`< ID_OF_PARENT_QUERY AND `date` IS NOT NULL LIMIT 1),
              (SELECT `date` FROM `table` WHERE `id`> ID_OF_PARENT_QUERY AND `date` IS NOT NULL LIMIT 1)
         ))

    WHERE `date` IS NULL

Answer 1

UPDATE tableX AS t
  JOIN tableX AS next
    ON next.id =
       ( SELECT MIN(n.id)
         FROM tableX AS n
         WHERE n.id > t.id
           AND n.dateX IS NOT NULL
       )
  JOIN tableX AS prev 
    ON prev.id =
       ( SELECT MAX(p.id)
         FROM tableX AS p
         WHERE p.id < t.id
           AND p.dateX IS NOT NULL
       )
SET t.dateX = prev.dateX + INTERVAL (DATEDIFF(next.dateX, prev.dateX) / 2) DAY
WHERE t.dateX IS NULL