当列值更改时MySQL选择行 [英] Mysql select row when column value changed

问题描述

我需要帮助或想法如何编写它，一条mysql语句.当特定列的值更改时，我需要选择一行.此后具有相同值的行不应选择.例如，我们有一个这样的表，其中填充了以下值:

I need help or idea how to write it, a mysql statement. I need to select a row when a specific column changes its value. Rows after that with the same value should not be selected. For example we have a table like this, populate with this values:

ID status_1 status_2 status_3 timestamp
1    0        0          0     2016-01-01 00:00:00
2    0        0          1     2016-01-01 01:00:00
3    0        1          0     2016-01-01 02:00:00
4    0        1          1     2016-01-01 03:00:00
5    1        0          0     2016-01-01 04:00:00
6    1        0          1     2016-01-01 05:00:00
7    1        1          0     2016-01-01 06:00:00
8    1        1          1     2016-01-01 07:00:00

如果我想在status_1更改时选择行，则查询应选择ID为1和5的行，如果我正在使用status_2的ID为:1、3、5、7的行，并且如果我正在使用status_3所有ID.希望有人可以在过去的所有时间里为我提供帮助.

If i wanna select the rows when the status_1 change, the query should select the rows with ID 1 AND 5, if i am working with status_2 rows with IDs: 1, 3, 5, 7, and if i am working with status_3 all IDs. Hope someone can help me with all the times in the past.

预先感谢

推荐答案

MySQL user defined variables在这种情况下会为您提供帮助.

MySQL user defined variables would help you in this case.

每次看到新状态时，将1作为行号分配给相应的行.

Every time you see a new status assign 1 as row number to the corresponding row.

如果您看到的状态与上一行相同，请分配一个递增的行号.

And if you see the same status that you saw in the previous row then assign an incremented row number instead.

这样，您最终可以过滤仅具有row number = 1的记录.这些特定记录实际上与前一行相比显示出差异

This way you can finally filter the records having row number = 1 only. These particular records are actually showing difference comparing to their immediate previous row

SELECT 
*
FROM 
(
 SELECT 
  *,
  IF(@prevStatus = YT.status_1, @rn := @rn + 1,
    IF(@prevStatus := YT.status_1, @rn := 1, @rn := 1)
  ) AS rn
 FROM your_table YT
 CROSS JOIN 
 (
  SELECT @prevStatus := -1, @rn := 1
 ) AS var 
 ORDER BY YT.ID
) AS t
WHERE t.rn = 1
ORDER BY t.ID

这篇关于当列值更改时MySQL选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！