SQL:选择列值与上一行不同的行 [英] SQL: selecting rows where column value changed from previous row

问题描述

假设我有这个(MySQL)数据库，按增加的时间戳排序:

Let's say I have this (MySQL) database, sorted by increasing timestamp:

Timestamp   System StatusA StatusB 
2011-01-01     A      Ok     Ok      
2011-01-02     B      Ok     Ok     
2011-01-03     A     Fail   Fail     
2011-01-04     B      Ok    Fail     
2011-01-05     A     Fail    Ok      
2011-01-06     A      Ok     Ok      
2011-01-07     B     Fail   Fail    

如何从该系统的上一行中选择StatusA更改的行? StatusB无关紧要(我在此问题中显示该信息只是为了说明每个系统在状态A不变的情况下可能会有很多连续的行).在上面的示例中，查询应返回行2011-01-03(对于SystemA，StatusA在2011-01-01和2011-01-03之间进行了更改)，2011-01-06、2011-01-07.

How do I select the rows where StatusA changed from the previous row for that system? StatusB doesn't matter (I show it in this question only to illustrate that there may be many consecutive rows for each system where StatusA doesn't change). In the example above, the query should return the rows 2011-01-03 (StatusA changed between 2011-01-01 and 2011-01-03 for SystemA), 2011-01-06, 2011-01-07.

查询应在具有成千上万条记录的表中快速执行.

The query should execute quickly with the table having tens of thousands of records.

谢谢

推荐答案

SELECT a.*
FROM tableX AS a
WHERE a.StatusA <>
      ( SELECT b.StatusA
        FROM tableX AS b
        WHERE a.System = b.System
          AND a.Timestamp > b.Timestamp
        ORDER BY b.Timestamp DESC
        LIMIT 1
      ) 

但是您也可以尝试使用此方法(在(System,Timestamp)上使用索引:

But you can try this as well (with an index on (System,Timestamp):

SELECT System, Timestamp, StatusA, StatusB
FROM
  ( SELECT (@statusPre <> statusA AND @systemPre=System) AS statusChanged
         , System, Timestamp, StatusA, StatusB
         , @statusPre := StatusA
         , @systemPre := System
    FROM tableX
       , (SELECT @statusPre:=NULL, @systemPre:=NULL) AS d
    ORDER BY System
           , Timestamp
  ) AS good
WHERE statusChanged ;

这篇关于SQL:选择列值与上一行不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！