sql查询过滤相邻重复数据

Question

表中有数据按照排序列col排序，查询的时候要过滤【相邻】的某些字段重复数据,
并保存排序列col最小的那个值（本来存的是时间，原理应该是一样的
如:

create table tb (
    eat int,
    drink int,
    col int;
)

insert into tb(eat ,drink ,col) values(1,1,1);
insert into tb(eat ,drink ,col) values(1,1,2);
insert into tb(eat ,drink ,col) values(2,3,3);
insert into tb(eat ,drink ,col) values(2,3,4);
insert into tb(eat ,drink ,col) values(1,1,5);
insert into tb(eat ,drink ,col) values(1,1,6);
insert into tb(eat ,drink ,col) values(4,5,7);
insert into tb(eat ,drink ,col) values(5,6,8);
insert into tb(eat ,drink ,col) values(2,3,9);

查询过滤掉ear,drink 相邻且相同的数据，并保留数据跳变对应的col值
查询出来的结果应该如下
1,1,1
2,3,3
1,1,5
4,5,7
5,6,8
2,3,9
大佬求救

Answer 1

因为是相邻取一条，所以顺序会影响最后的结果，我增加了一个 ID 字段，保证结果一致

SET @idx = 1;
SET @eat   = -1;
SET @drink = -1;

SELECT 
t1.id
,t1.eat
,t1.drink
,t1.col
FROM (
SELECT 
CASE
 WHEN @eat=t0.eat AND @drink=t0.drink THEN @idx := @idx+1
 ELSE @idx:=1
END
,@eat:=t0.eat
,@drink:=t0.drink 
,@idx AS idx
,t0.*
FROM tb t0
ORDER BY id ASC
) t1
WHERE t1.idx = 1
ORDER BY id ASC

查询结果如下：

Answer 2

用group by 分组，取最小值就可以了吧，如：

select eat, drink, min(col)
from tb
group by eat, drink

Answer 3

Mysql8:

SELECT eat, drink, col
  FROM (SELECT *, row_number() OVER(PARTITION BY eat, drink ORDER BY col ASC) AS n from order_items)  t 
 WHERE n = 1