mysql数据行之间的对比,MySQL-查找同一表的行之间的差异

I have a table containing aggregated results with timestamps - meaning each result per time is the total so far:

date | time | ip | result

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

2011-03-01 | 10:00 | 10.0.0.1 | 200

2011-03-01 | 11:00 | 10.0.0.1 | 303

2011-03-01 | 12:00 | 10.0.0.1 | 415

2011-03-01 | 13:00 | 10.0.0.1 | 628

2011-03-01 | 10:00 | 10.0.0.2 | 198

2011-03-01 | 11:00 | 10.0.0.2 | 234

2011-03-01 | 12:00 | 10.0.0.2 | 373

2011-03-01 | 13:00 | 10.0.0.2 | 512

I'm trying to formulate a query that'll get the deltas between each time range:

date | time | ip | diff

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

2011-03-01 | 10:00 | 10.0.0.1 | 200

2011-03-01 | 11:00 | 10.0.0.1 | 103

2011-03-01 | 12:00 | 10.0.0.1 | 112

2011-03-01 | 13:00 | 10.0.0.1 | 213

2011-03-01 | 10:00 | 10.0.0.2 | 198

2011-03-01 | 11:00 | 10.0.0.2 | 36

2011-03-01 | 12:00 | 10.0.0.2 | 139

2011-03-01 | 13:00 | 10.0.0.2 | 139

...

So each row per date / ip grouping subtracts the one before it (or 0).

Any simple way to do this? thanks.

解决方案

Here is a solution without variables. I assume you have your initail data in a table called thetable.

SELECT date, time, ip,

result - IFNULL( (

SELECT MAX( result )

FROM thetable

WHERE ip = t1.ip

AND ( date < t1.date

OR date = t1.date AND time < t1.time )

) , 0) AS diff

FROM thetable AS t1

ORDER BY ip, date, time

Here we get the previous value with a subselect (the maximal result of the preceding timestamps from the same ip). IFNULL gives us a 0 if this was the first value, so initial results are displayed correctly.

I also recommend adding the following index to thetable:

CREATE INDEX sort1 ON thetable (ip, date, time);