MySQL 15 分钟内的第一行

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 9 年前。

Improve this question

我想要 15 分钟间隔内的第一条记录。

我有一个 MySQL 表，其中包含大约 1 分钟间隔的日期时间行，我想检索第一个记录，然后在接下来的 15 分钟内再次检索第一个记录。我很困惑是使用 join 还是 MySQL 变量还是嵌套查询。即使您不知道正确的指导我应该使用变量或连接或嵌套什么？

具体来说，我们有一个表，其中一列 date_timestamp 为

+---------------------+
|   date_timestamp    |
+---------------------+
| 2014-01-07 11:49:42 |
| 2014-01-07 11:50:12 |
| 2014-01-07 11:50:31 |
| 2014-01-07 11:50:42 |
| 2014-01-07 11:51:22 |
| 2014-01-07 11:51:42 |
| 2014-01-07 11:52:52 |
| 2014-01-07 11:53:12 |
| 2014-01-07 11:53:32 |
| 2014-01-07 11:54:22 |
| 2014-01-07 11:55:42 |
| 2014-01-07 11:58:02 |
| 2014-01-07 11:59:22 |
| 2014-01-07 12:00:02 |
| 2014-01-07 12:00:42 |
| 2014-01-07 12:01:32 |
| 2014-01-07 12:01:52 |
| 2014-01-07 12:02:22 |
| 2014-01-07 12:03:42 |
| 2014-01-07 12:04:42 |
| 2014-01-07 12:05:02 |
+---------------------+

i want the output to be if i select 5 minute interval 
+---------------------+
|   date_timestamp    |
+---------------------+
| 2014-01-07 11:49:42 |
| 2014-01-07 11:54:22 |
| 2014-01-07 11:59:22 |
| 2014-01-07 12:04:42 |
+---------------------+

Answer 1

更新(调整以满足您的示例数据并忽略秒):

select date_timestamp from (
select 
date_timestamp
, if(date_format(date_timestamp, '%Y-%m-%d %H:%i:00') = @a, 1, 0) as equals
, if(date_format(date_timestamp, '%Y-%m-%d %H:%i:00') = @a, @a:=@a + interval 5 minute, @a) as mya
from my_table, 
(select @a := (select date_format(min(date_timestamp), '%Y-%m-%d %H:%i:00') from my_table)) var_init 
order by date_timestamp
) sq
where equals = 1;

• 看到它在 中实时运行 sqlfiddle

原始答案:

我会使用用户定义的变量。

测试数据:

create table interv(id int auto_increment primary key, myd datetime);

insert into interv(myd) values 
(now()), 
(now() + interval 7 minute), 
(now() + interval 9 minute), 
(now() + interval 15 minute), 
(now() + interval 16 minute),
(now() + interval 30 minute), 
(now() + interval 35 minute);

查询:

select id, myd from (
select 
id
, myd
, if(myd = @a, 1, 0) as equals
, if(myd = @a, @a:=@a + interval 15 minute, @a) as mya
from interv, 
(select @a := (select min(myd) from interv)) var_init 
order by myd
) sq
where equals = 1;

结果:

| ID |                          MYD |
|----|------------------------------|
|  1 | March, 28 2014 09:03:23+0000 |
|  4 | March, 28 2014 09:18:23+0000 |
|  6 | March, 28 2014 09:33:23+0000 |

• 看到它在 中实时运行 sqlfiddle