mysql - 查找序列中的错误

Question

我有一份事件变更日志，记录了变为事件/非事件的官员。

OfficerID  ChangeTo   ChangeDate
1          active     2017-05-01
1          active     2017-05-02
1          inactive   2017-05-04
6          active     2013-09-09
6          inactive   2016-04-14
6          recruit    2016-06-22
6          active     2016-06-23
6          inactive   2017-04-30

在上述情况下，ID 1 的官员的有效期为 5 月 1 日至 5 月 4 日。

这本质上是一项“内务管理”任务。第二行不是必需的，应删除。我想在链接到时间表上的事件的 MySQL 过程中执行此操作。我需要一个可以识别这些行的查询，但我不确定如何识别。

在以前的系统中，我循环遍历有序列表并将当前值与前一行的值进行比较。我读到不鼓励在 MySQL 中使用循环，所以我试图弄清楚如何单独使用查询来做到这一点。

我尝试了以下方法:

SELECT
    a.ActivityID, a.OfficerID, a.ChangeTo, a.ChangeDate
FROM
    tbl_Officers_Activity as a
    INNER JOIN tbl_Officers_Activity AS b
        ON a.OfficerID = b.OfficerID
        AND a.ChangeDate > b.ChangeDate
        AND a.ChangeTo = b.ChangeTo
    INNER JOIN tbl_Officers_Activity AS c
        ON a.OfficerID = c.OfficerID
        AND a.ChangeDate < c.ChangeDate
        AND a.ChangeTo <> c.ChangeTo
ORDER BY
    OfficerID,
    ChangeDate;

我希望我能以某种方式将我需要的标准嵌入到连接中，但我不知所措。任何帮助将不胜感激。

Answer 1

这就是你所需要的

SQLFIddle Demo

select a1.officerid,a1.changedate,a1.changeto_a as changeto
From 
(select a.officerid,a.changedate,max(a.changeto) as changeto_a,count(*) as rnk
from tbl_Officers_Activity a
inner join tbl_Officers_Activity b
on a.OfficerID=b.OfficerID
and a.ChangeDate>=b.ChangeDate
group by a.officerid,a.changedate) a1
left join
(select a.officerid,a.changedate,max(a.changeto) as changeto_b,count(*) +1 as rnk
from tbl_Officers_Activity a
inner join tbl_Officers_Activity b
on a.OfficerID=b.OfficerID
and a.ChangeDate>=b.ChangeDate
group by a.officerid,a.changedate) b1
on a1.officerid=b1.officerid
and a1.rnk=b1.rnk 
where changeto_a = changeto_b

说明:

MySQL没有row_Number函数，所以首先我必须导出它。我使用此查询来获取 row_number，它在查询中的名称为 rnk。将该表命名为 a1。

(select a.officerid,a.changedate,max(a.changeto) as changeto_a,count(*) as rnk
from tbl_Officers_Activity a
inner join tbl_Officers_Activity b
on a.OfficerID=b.OfficerID
and a.ChangeDate>=b.ChangeDate
group by a.officerid,a.changedate)

现在由于 MySQL 也没有 LEAD 函数，我再次使用上面的查询来派生它，并将 rnk 更改为 rnk+1，将其称为 b1。

现在要复制LEAD，我左连接 a1与b1

现在使用 where 子句查找相同的 changeto，您可以获得输出。