选择值更改 [英] Select on value change
问题描述
在 MySQL
数据库中,我有一个看起来像这样的表:
I have a table that looks like this in a MySQL
database:
CREATE TABLE IF NOT EXISTS Example(Batch_Num int, Time DATETIME);
INSERT INTO Example
VALUES
(1,'2020-12-04 05:06:12'),
(1,'2020-12-04 05:06:13'),
(1,'2020-12-04 05:06:14'),
(2,'2020-12-04 05:06:20'),
(2,'2020-12-04 05:07:12'),
(2,'2020-12-04 05:07:20'),
(1,'2020-12-04 05:07:25'),
(1,'2020-12-04 05:07:35'),
(3,'2020-12-04 05:07:35');
我想选择 Batch_Num
与上一个值不同的所有行,包括第一个:
I would like to select all lines where the Batch_Num
is different from the previous value including the first one:
+----------+-----------------------+
| BatchNum | Change_Time |
+----------+-----------------------+
| 1 | '2020-12-04 05:06:12' |
| 2 | '2020-12-04 05:06:20' |
| 1 | '2020-12-04 05:07:25' |
| 3 | '2020-12-04 05:07:35' |
+----------+-----------------------+
是否有关键字可以访问上一行以将其与当前行进行比较?还是通过其他方式将一条线与其之前的线进行比较?
Is there a keyword maybe to access the previous line to compare the to the current line? Or some other way to compare a line to the line before it?
推荐答案
这是种空缺"问题.孤岛是具有相同 batchnum
的相邻行,您需要每个孤岛的开始.
This is a kind of gaps-and-islands problem. Islands are adjacent rows that have the same batchnum
, and you want the start of each island.
在这里,最简单的方法可能是 lag()
:
Here, the simplest approach is probably lag()
:
select *
from (
select e.*,
lag(batchnum) over(order by time) lag_batchnum
from example e
) e
where not lag_batchnum <=> batchnum
请注意,这需要MySQL 8.0.在早期版本中,一种替代方法是使用相关子查询:
Note that this requires MySQL 8.0. In earlier versions, one alternative uses a correlated subquery:
select e.*
from example e
where not batchnum <=> (
select e1.batchnum
from example e1
where e1.time < e.time
order by e1.time desc
limit 1
)
这是 DB Fiddle上的演示 .
Here is a demo on DB Fiddle.
这篇关于选择值更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!