mysql 实现相邻两条数据日期相减

 

 

参考：https://blog.csdn.net/yjgithub/article/details/75433345

 

功能：获取某一天的创建用户时间间隔

 

 

1. 表结构数据如下

 

 

2. 查询结果如下

 

 

3. SQL语句如下

 

SELECT
	two.id,
	two.user_name userName,
	two.create_time createTime,
	two.currentSecond,
	two.previousSecond,
	IF (two.previousSecond = 0, 0, two.currentSecond - two.previousSecond) intervalSecond
FROM
	(
		SELECT
			one.id,
			one.user_name,
			one.create_time,
			time_to_sec(one.create_time) currentSecond,
			@one.createTime AS previousSecond,
			@one.createTime := time_to_sec(one.create_time)
		FROM
			t_user_base one,
			(SELECT @one.createTime := 0) tmp
		ORDER BY
			one.create_time
	) two