SQL:选择列值从上一行更改的行

2021-11-20 00:00:00 sql mysql

假设我有这个(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 无关紧要(我在这个问题中显示它只是为了说明每个系统可能有许多连续的行，其中 StatusA 没有改变).在上面的示例中，查询应返回行 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 ;

相关文章