mysql求每一个顾客购买商品的相邻时间间隔

t_punch_cade

select a.*,(@i := @i + 1) as ord_num from t_punch_cade a,(select @i := 1) d order by user_id,punch_time

select a.*,(@j := @j + 1) as ord_num from t_punch_cade a,(select @j := 0) c order by user_id,punch_time

select A.user_id,A.punch_time,TIMESTAMPDIFF(HOUR,A.punch_time,B.punch_time)
from(
    select a.*,(@i := @i + 1) as ord_num from t_punch_cade a,(select @i := 1) d order by user_id,punch_time
) as A LEFT JOIN (
    select a.*,(@j := @j + 1) as ord_num from t_punch_cade a,(select @j := 0) c order by user_id,punch_time

)as B on A.ord_num=B.ord_num and A.user_id=B.user_id

 

 利用两个表错位相减就可以得到每一个顾客的相邻时间的购买时间间隔