mysql 累计值_MySQL查询获取累计值

Date | VALUE

--------------------------------

"2011-01-14 11:00" | 2

"2011-01-14 19:30" | 5

"2011-01-15 13:30" | 7

"2011-01-15 23:50" | 6

"2011-01-16 9:30" | 10

"2011-01-16 18:30" | 15

的查询获取最新的日常值和先前最新每日价值之间的差额/累积。

SELECT

t1.dt AS date,

t1.value - t2.value AS value

FROM

(SELECT DATE(date) dt, MAX(date), VALUE as value FROM table GROUP BY dt) t1

JOIN

(SELECT DATE(date) dt, MAX(date), VALUE as value FROM table GROUP BY dt) t2

ON t1.dt = t2.dt + INTERVAL 1 DAY

所以结果是一样的东西：

Date | VALUE

---------------------------

"2011-01-15 00:00" | -1

"2011-01-16 00:00" | 6

但我需要从第一天也累计值。一般来说，如果前一天不存在，我需要一天的累计值。事情是这样的：

Date | VALUE

---------------------------

"2011-01-14 00:00" | 3

"2011-01-15 00:00" | -1

"2011-01-16 00:00" | 6

2012-02-10

Jaime M.