时间戳

首页 > 解决方案 > MySQL - 如何根据前一行中的值查找行

mysql - MySQL - 如何根据前一行中的值查找行

问题描述

我有一个设置表：

create table settings(
    id int
  , date datetime
  , user varchar(20)
  , status varchar(10)
  );

insert into settings values
 (1 , '2020-07-10 14:36:06', 'user1', 'INACTIVE'),
 (1 , '2020-07-10 14:39:54', 'user1', 'ACTIVE'),
 (1 , '2020-07-10 14:44:06', 'user1', 'INACTIVE'),
 (1 , '2020-07-10 14:46:06', 'user2', 'INACTIVE'),
 (1 , '2020-07-10 14:49:06', 'user2', 'SCHEDULED'),
 (1 , '2020-07-10 14:55:06', 'user2', 'ACTIVE'),
 (2 , '2020-07-12 14:36:06', 'user1', 'INACTIVE'),
 (2 , '2020-07-13 14:39:54', 'user1', 'INACTIVE'),
 (2 , '2020-07-13 16:44:06', 'user1', 'ACTIVE'),
 (2 , '2020-07-15 14:46:06', 'user3', 'INACTIVE'),
 (2 , '2020-07-15 15:49:06', 'user3', 'INACTIVE'),
 (2 , '2020-07-17 14:46:06', 'user3', 'ACTIVE')
 ;

带有插入代码的示例代码在sql fiddle

该过程从INACTIVE状态开始，由系统安排，然后用户可以更改为SCHEDULED或ACTIVE。

我需要找到具有ACTIVE状态的行，但只有这些之前是具有INACTIVE按用户分组的状态的行。特定情况（不确定是否有可能）查找具有ACTIVE状态的行，但如果先前的行具有SCHEDULED -> INACTIVE 顺序。

我在下面的屏幕截图中选择了应该返回哪些行：

在此处输入图像描述

我正在尝试使用它，但结果应该返回当前行下的前一行。

select * from (  
select @rownum:=@rownum+1 row, a.* 
from settings a
where a.status = 'ACTIVE'
) as cr

LEFT JOIN (SELECT  @rownum2:=@rownum2+1 row, b.* FROM settings b
where b.status = 'INACTIVE' ) as pr
on cr.id = pr.id and cr.date > pr.date

标签： mysqlnested-query

解决方案

您可以使用自加入来执行此操作。进行自连接后，从一个表（比如 A）获取每个日期的 ACTIVE 时间，从另一个表（比如 B）获取 Inactive 时间 where B.date<A.date。

然后您可以在两个日期执行其他操作，例如获取持续时间或其他内容。

在这里检查小提琴：http ://sqlfiddle.com/#!9/84789e/23

询问 ：

SELECT A.user,max(B.date) as Inactive,min(A.date) as Active 
FROM settings A JOIN settings B
ON A.id=B.id and A.user=B.user
WHERE A.status = 'ACTIVE'
AND B.status = 'INACTIVE'
AND B.date<A.date
GROUP BY  A.date,A.user

推荐阅读