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
![](https://img2020.cnblogs.com/blog/1703002/202101/1703002-20210102004648128-2037530840.png)
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
![](https://img2020.cnblogs.com/blog/1703002/202101/1703002-20210102004745526-1212447724.png)
利用两个表错位相减就可以得到每一个顾客的相邻时间的购买时间间隔