计算mysql中连续行之间的日期差

Question

我在mysql中有一个TABLE，其中有列：

id, created_time, name

每一行都有id和创建日期时间。例如：

id     created_on              name
1      2020-12-03 13:15:09     john
2      2020-11-08 14:11:19     john
3      2020-10-06 14:11:19     john
4      2020-09-12 14:11:19     john

想要显示2行之间的差异大于30天的结果

预期输出：-

id    created_on             name
2     2020-11-08 14:11:19    john
4     2020-09-12 14:11:19    john

我尝试过以下查询，但失败了，因为它显示了所有数据。

SELECT id
, name
FROM
( SELECT MIN(created_on) start_time
, id
, name 
FROM table1 
WHERE name = 'john'  
GROUP  
BY id
) start_action
JOIN
( SELECT MAX(created_on) close_time 
, id
, name 
FROM table1 
WHERE name = 'john' 
GROUP 
BY id
) close_action
USING (id,name)
WHERE name = 'john'

我正在使用Mysql 5.6版本请求帮助

Answer 1

您可以在这里使用LEAD作为MySQL 8+解决方案：

WITH cte AS (
SELECT *, LEAD(created_time) OVER (ORDER BY id) lead_created_time,
LAG(created_time) OVER (ORDER BY id) lag_created_time
FROM yourTable
)
SELECT id, created_time
FROM cte
WHERE ABS(DATEDIFF(lead_created_time, created_time)) > 30 OR
ABS(DATEDIFF(lag_created_time, created_time)) > 30;

演示