以时间列举例:
一个表 :power_on ,里面有一列:ctime 时间格式 ,计算时间列的间隔,也就是差值
TIMESTAMPDIFF :计算两个时间差值的函数
重点 : @rownum
(SELECT @rownum:=0) t2 表示重新生成了一个新表,里面存的行数
SELECT a1.ctime,TIMESTAMPDIFF(MINUTE,a2.ctime2,a1.ctime) AS diff FROM
( SELECT t1.ctime ,@rownum:=@rownum+1 AS rownum FROM power_on t1,(SELECT @rownum:=0) t2
ORDER BY ctime )a1
LEFT JOIN
( SELECT t1.ctime AS ctime2,@index :=@index +1 AS rownum2 FROM power_on t1,(SELECT @index :=1) t2
ORDER BY ctime )a2
ON a1.rownum = a2.rownum2