mysql 计算某列的差值

以时间列举例：

一个表 ：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