SQL:选择列值与上一行不同的行

本文介绍了SQL:选择列值与上一行不同的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我有这个(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 ;