我每5分钟自动填充一个数据库,但每天我有一个小时的样本等于零,如下所示:
时间 | |
---|---|
11:50 | 1800 |
11:55 | 1500 |
00:00 | 0 |
00:05 | 0 |
1:00 | 1600 |
这应该可以做到:
SELECT
Time,
CASE
WHEN Power <> 0 THEN Power
ELSE ( -- need last nonzero Power
SELECT
Power
FROM
TableName X -- alias to avoid namespace confusion
WHERE
Power <> 0
AND Time <= TableName.Time
ORDER BY
Time DESC
LIMIT 1
)
END AS Power
FROM
TableName
ORDER BY
Time
;
子查询获取最近的非零幂。出于性能原因,我添加了CASE语句:除非需要,否则不希望运行子查询。