mysql表单内缺少值,插入MySQL表中的缺失值

I have some intraday stock data saved into a MySQL table which looks like this:

+----------+-------+

| tick | quote |

+----------+-------+

| 08:00:10 | 5778 |

| 08:00:11 | 5776 |

| 08:00:12 | 5778 |

| 08:00:13 | 5778 |

| 08:00:14 | NULL |

| 08:00:15 | NULL |

| 08:00:16 | 5779 |

| 08:00:17 | 5778 |

| 08:00:18 | 5780 |

| 08:00:19 | NULL |

| 08:00:20 | 5781 |

| 08:00:21 | 5779 |

| 08:00:22 | 5779 |

| 08:00:23 | 5779 |

| 08:00:24 | 5778 |

| 08:00:25 | 5779 |

| 08:00:26 | 5777 |

| 08:00:27 | NULL |

| 08:00:28 | NULL |

| 08:00:29 | 5776 |

+----------+-------+

As you can see, there are some points where no data is available (quote is NULL). What I would like to do is a simple step interpolation. This means each NULL value should be updated with the last value available. The only way I managed to do this is with cursors, which is pretty slow due to the large amount of data. I'm basically searching something like this:

UPDATE table AS t1

SET quote = (SELECT quote FROM table AS t2

WHERE t2.tick < t1.tick AND

t2.quote IS NOT NULL

ORDER BY t2.tick DESC

LIMIT 1)

WHERE quote IS NULL

Of course this query will not work, but this is how it should look like.

I would appreciate any ideas on how this can be solved without cursors and temp tables.

解决方案

This should work:

SET @prev = NULL;

UPDATE ticks

SET quote= @prev := coalesce(quote, @prev)

ORDER BY tick;

BTW the same trick works for reading:

SELECT t.tick, @prev := coalesce(t.quote, @prev)

FROM ticks t

JOIN (SELECT @prev:=NULL) as x -- initializes @prev

ORDER BY tick