mysql 获取每个时间段之间的某条数据

Question

现在项目上遇到一个问题需要用sql来查询某个时间段内的某条数据(例如从2022-2-1到2022-2-20日内的每隔一个小时中的第一条数据)

下面的为数据库表

Answer 1

题目没有说要不要根据线路分组,下面这个sql是取每条线路每个小时的第一条

--测试数据
create table test_20220328_a(id int,linenumber int,createtime datetime);
insert into test_20220328_a values(1,1,'2022-02-01 01:21:00');
insert into test_20220328_a values(2,1,'2022-02-01 01:31:00');
insert into test_20220328_a values(3,1,'2022-02-01 02:21:00');
insert into test_20220328_a values(4,1,'2022-02-01 02:51:00');
insert into test_20220328_a values(5,1,'2022-02-02 04:21:00');
insert into test_20220328_a values(6,1,'2022-02-02 05:21:00');
insert into test_20220328_a values(7,1,'2022-02-02 09:21:00');
insert into test_20220328_a values(8,2,'2022-02-01 01:21:00');
insert into test_20220328_a values(9,2,'2022-02-01 01:31:00');
insert into test_20220328_a values(10,2,'2022-02-01 02:21:00');
insert into test_20220328_a values(11,2,'2022-02-01 02:51:00');
insert into test_20220328_a values(12,2,'2022-02-02 04:21:00');
insert into test_20220328_a values(13,2,'2022-02-02 05:21:00');
insert into test_20220328_a values(14,2,'2022-02-02 09:21:00');

--查询sql
select * from (
select a.*,
row_number() over(partition by linenumber,date_format(createtime, '%Y%m%d%H') order by createtime,id) rn 
from test_20220328_a a 
where createtime between '2022-02-01 00:00:00' and '2022-02-20 23:59:59'
) t where rn=1

如果不要按线路分组,把开窗函数里的linenumber干掉就是了