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;