MySQL相邻行相减

本文主要是介绍MySQL相邻行相减，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

为计算日期间隔，数据如下

需要算出相邻id的日期差值。

SELECT A.id,A.start_date AS a_start_date,B.start_date AS b_start_date,DATEDIFF(DATE_FORMAT(A.start_date,'%Y-%m-%d'),DATE_FORMAT(B.start_date,'%Y-%m-%d')) AS diff
FROM (
SELECT a.*,(@i :=@i+1) AS ord_num FROM period a,(SELECT @i :=1) d WHERE user_id=100000276	ORDER BY start_date DESC 
) AS A LEFT JOIN(
SELECT a.*,(@j :=@j+1) AS ord_num FROM period a,(SELECT @j :=0) c WHERE user_id=100000276 ORDER BY start_date DESC
)AS B ON A.ord_num=B.ord_num AND A.user_id =B.user_id;

这篇关于MySQL相邻行相减的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！