sql查询最近两次连续注册的时间

准备数据源

USE test;
CREATE TABLE login(
id INT NOT NULL,
user_id VARCHAR(255),
login_time DATETIME
)

插入数据

INSERT INTO login(id,user_id,login_time) VALUES (1,'u1','2020-04-28 9:20:00');
INSERT INTO login(id,user_id,login_time) VALUES (1,'u1','2020-4-25 9:37.00');
INSERT INTO login(id,user_id,login_time) VALUES (1,'u1','2020-4-20 9:20:00');
INSERT INTO login(id,user_id,login_time) VALUES (1,'u2','2020-4-28 9:42:00');
INSERT INTO login(id,user_id,login_time) VALUES (1,'u2','2020-4-22 9:20:00');

查询数据

对数据进行分析

方法一

USE test;
SELECT 
*,
DATEDIFF(b.pre,b.login_time ) AS diff
FROM
(	SELECT 
	id,
	user_id,
	login_time,
	@a.login_time AS pre,
	@a.login_time:= a.login_time
	FROM login a ,(SELECT @a.login_time:=0)r 
	WHERE a.user_id='u1'
 )b order by b.login_time limit 1;

方法二

use test;
SELECT
DATEDIFF(payl.login_time,payl.btime) AS diff
FROM
(	
	SELECT 
	pl.user_id,
	pl.login_time,
	pl.btime
	FROM
		(SELECT  
		a.id, 
		a.user_id,
		a.login_time, 
		b.login_time AS btime 
		FROM login  a,login b 
		WHERE a.user_id='u1' and b.user_id='u1'
		ORDER BY a.login_time DESC,btime DESC LIMIT 2)pl
	WHERE pl.login_time != pl.btime LIMIT 1
	)payl;