ID Timestamp Value                                                                
   -- --------- ----- 
   1   11:59.54   10 
   1   12.04.00   20 
   1   12.12.00   31 
   1   12.16.00   10 
   1   12.48.00   05 

我希望结果集为

   ID Timestamp Value 
   -- --------- ----- 
   1   11:59.54   10 
   1   12:00:00   10 
   1   12.04.00   20 
   1   12.12.00   31 
   1   12:15:00   31 
   1   12:16.00   10 
   1   12:30:00   10 
   1   12:45:00   10 
   1   12.48.00   05 

请您参考如下方法:

更多的咖啡可能会导致更简单的解决方案，但请考虑以下...

DROP TABLE IF EXISTS my_table; 
 
CREATE TABLE my_table 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 
,timestamp TIMESTAMP 
,value INT NOT NULL 
); 
 
INSERT INTO my_table VALUES 
(1   ,'11:59:54',10), 
(2   ,'12:04:00',20), 
(3   ,'12:12:00',31), 
(4   ,'12:16:00',10), 
(5   ,'12:48:00',05); 

...此外，我有一个整数表，如下所示:

SELECT * FROM ints; 
+---+ 
| i | 
+---+ 
| 0 | 
| 1 | 
| 2 | 
| 3 | 
| 4 | 
| 5 | 
| 6 | 
| 7 | 
| 8 | 
| 9 | 
+---+ 

所以...

SELECT a.timestamp 
     , b.value 
  FROM  
     ( SELECT x.* 
            , MIN(y.timestamp) min_timestamp 
         FROM  
            ( SELECT timestamp 
                FROM my_table 
               UNION 
              SELECT SEC_TO_TIME((i2.i*10+i1.i)*900) 
                FROM ints i1 
                   , ints i2  
               WHERE SEC_TO_TIME((i2.i*10+i1.i)*900)  
                     BETWEEN (SELECT MIN(timestamp) FROM my_table)  
                         AND (SELECT MAX(timestamp) FROM my_table)  
               ORDER  
                  BY timestamp 
            ) x 
         LEFT 
         JOIN my_table y 
           ON y.timestamp >= x.timestamp 
        GROUP  
           BY x.timestamp 
    ) a 
 JOIN my_table b 
   ON b.timestamp = min_timestamp; 
 
+-----------+-------+ 
| timestamp | value | 
+-----------+-------+ 
| 11:59:54  |    10 | 
| 12:00:00  |    20 | 
| 12:04:00  |    20 | 
| 12:12:00  |    31 | 
| 12:15:00  |    10 | 
| 12:16:00  |    10 | 
| 12:30:00  |     5 | 
| 12:45:00  |     5 | 
| 12:48:00  |     5 | 
+-----------+-------+