I try to use the previous column value and the current column value to make some conditions. This is when the previous value and current value are equal to 1, then the current value return as 0 and updates to another table in MySQL. However I am not sure how to call the previous data from my SQL table and make the comparison.. hopefully, can get some advice.
| id | value | time |
|---|---|---|
| 1 | 0 | 2022-08-03 10:27:24 |
| 2 | 0 | 2022-08-03 10:27:29 |
| 3 | 1 | 2022-08-03 10:27:32 |
| 4 | 1 | 2022-08-03 10:27:32 |
| 5 | 1 | 2022-08-03 10:27:34 |
The data that I need to get
| id | value | time |
|---|---|---|
| 1 | 0 | 2022-08-03 10:27:24 |
| 2 | 0 | 2022-08-03 10:27:29 |
| 3 | 0 | 2022-08-03 10:27:32 |
| 4 | 0 | 2022-08-03 10:27:32 |
| 5 | 1 | 2022-08-03 10:27:34 |
CodePudding user response：
Use sub query:
SELECT curr.*, (SELECT prev.time FROM my_table prev WHERE id = curr.id - 1) FROM my_table curr
Now as for the condition of
current value are equal to 1, then the current value return as 0
Try MySQL CASE.