blocks|key|2127891|text|您应该有一列时间，以达到所需的精度。然后，您可以将最后的结果排序到顶部，并在查询中使用limit+1。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2127892|无论如何，您应该有一个轮询机制来在每分钟内触发查询。|2127893|entityMap^0|17|7|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

You should have a column of time in to required precision. Then you can sort by time [last results to top] and use <code>limit 1</code> in the query.

Anyway you should have a polling mechanism to trigger the query in each minute.

blocks|key|149102|text|你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|149103|SELECT+col1,+col2,+col3,+colN,+MAX(TimeAndDateColumn)
FROM+table
GROUP+BY+DATE(TameAndDateColumn),+HOUR(TimeAndDateColumn),+Minute(TimeAndDateColumn)|code-block|syntax|javascript|149104|这里是SQL+Fiddle，这是一些其他表，但是只是为了看看这个例子是如何工作的.|offset|length|149105|吉尔！|149106|编辑(使用列名查询)|style|BOLD|149107|SELECT+id,+latitute,+longitude,+speed,+MAX(`time`)
FROM+YourTableName
GROUP+BY+DATE(`time`),+HOUR(`time`),+Minute(`time`)|149108|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!9/88a87/3^0|0|0|3|A|0|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@$I|13|J|14|1|15]]|A|$]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|17|8|@$I|18|J|19|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

Well you can do something like this:

<pre><code>SELECT col1, col2, col3, colN, MAX(TimeAndDateColumn)
FROM table
GROUP BY DATE(TameAndDateColumn), HOUR(TimeAndDateColumn), Minute(TimeAndDateColumn)
</code></pre>

Here is <a href="http://sqlfiddle.com/#!9/88a87/3" rel="nofollow">SQL Fiddle.</a> this is some other table but just to see how this work on example...

GL!

EDIT (query with your column name)

<pre><code>SELECT id, latitute, longitude, speed, MAX(`time`)
FROM YourTableName
GROUP BY DATE(`time`), HOUR(`time`), Minute(`time`)
</code></pre>

blocks|key|1085667|text|您的问题还不清楚，但我相信您正在寻找一个查询，该查询将过滤表的内容，以便在每个“日历分钟”中最多返回一条记录。也就是说，如果多个记录发生在同一日历分钟内，则只能得到其中一个记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1085668|试着做这样的事情：|1085669|SELECT+DATE_FORMAT(time_stamp,+'%25Y-%25m-%25d+%25H:%25i:00')+time_stamp,
+++++++AVG(lat)+lat,+AVG(lon)+lon
++FROM+table
+GROUP+BY+DATE_FORMAT(time_stamp,+'%25Y-%25m-%25d+%25H:%25i:00')+|code-block|syntax|javascript|1085670|这将使您在这一分钟的平均位置计算。诀窍是，DATE_FORMAT(time_stamp,+'%25Y-%25m-%25d+%25H:%25i:00')将time_stamp值截断到紧接之前的日历分钟，并由GROUP+BY聚合它们。|offset|length|style|CODE|1085671|你可以在每分钟内得到第一次或最后一次测量，但这很难。如果您的id值总是随着time_stamp的上升而上升，那么您可以提高效率。此查询将在每个日历分钟中获取第一个观察结果。|1085672|+SELECT+a.lat,+a.lon,+a.time_stamp
+++FROM+table+a
+++JOIN+(
++++++++++SELECT+MIN(id)+id+
++++++++++++FROM+table
+++++++++++GROUP+BY+DATE_FORMAT(time_stamp,+'%25Y-%25m-%25d+%25H:%25i:00')
++++++++)+b+ON+a.id+=+b.id|1085673|内部查询获取每个日历分钟内最早观察到的id，连接获取与这些id值关联的详细记录。如果您想得到最新的观察结果，请改用MAX(id)。|1085674|请注意。我是GROUP+BY的纯粹主义者。我不喜欢GROUP+BY。|1085675|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html^0|0|0|0|L|18|1U|A|2K|8|0|U|2|11|A|0|0|J|2|1L|7|0|6|8|P|8|P|8|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|17|8|@$K|18|L|19|M|N]|$K|1A|L|1B|M|N]|$K|1C|L|1D|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1E|8|@$K|1F|L|1G|M|N]|$K|1H|L|1I|M|N]]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1J|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1K|8|@$K|1L|L|1M|M|N]|$K|1N|L|1O|M|N]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1P|8|@$K|1Q|L|1R|M|N]|$K|1S|L|1T|M|N]]|9|@$K|1U|L|1V|1|1W]]|A|$]]|$1|W|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

Your question isn't clear, but I believe you're looking for a query that will filter the contents of your table to return at most one record for each "calendar minute." That is, if multiple records occur in the same calendar minute, you only get one of them.

Try something like this:

<pre><code>SELECT DATE_FORMAT(time_stamp, '%Y-%m-%d %H:%i:00') time_stamp,
 AVG(lat) lat, AVG(lon) lon
 FROM table
 GROUP BY DATE_FORMAT(time_stamp, '%Y-%m-%d %H:%i:00') 
</code></pre>

This will get you the average position computation in that minute. The trick is that <code>DATE_FORMAT(time_stamp, '%Y-%m-%d %H:%i:00')</code> truncates <code>time_stamp</code> values to the immediately preceding calendar minute, and <code>GROUP BY</code> aggregates them.

You can get the first, or last, measurement in each minute, but it's harder. If your <code>id</code> values always go up as your <code>time_stamp</code> goes up, you can make this efficient. This query will fetch the first observation in each calendar minute.

<pre><code> SELECT a.lat, a.lon, a.time_stamp
 FROM table a
 JOIN (
 SELECT MIN(id) id 
 FROM table
 GROUP BY DATE_FORMAT(time_stamp, '%Y-%m-%d %H:%i:00')
 ) b ON a.id = b.id
</code></pre>

The inner query fetches the <code>id</code> of the earliest observation in each calendar minute, and the JOIN fetches the detail records associated with those id values. If you want the latest observation, use <code>MAX(id)</code> instead.

Note. I am a <code>GROUP BY</code> purist. I don't like the <a href="https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html" rel="nofollow">nondeterministic behavior of the MySQL extension to <code>GROUP BY</code></a>.

blocks|key|2127968|text|SELECT+AVG(long),+AVG(lat),+timestamp
FROM+table
GROUP+BY+round(UNIX_TIMESTAMP(timestamp)+/+60)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2127969|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT AVG(long), AVG(lat), timestamp
FROM table
GROUP BY round(UNIX_TIMESTAMP(timestamp) / 60)
</code></pre>

I have records as per attached image. The last column has date and time of latitude and longitude recieved. Now i want to select one record per minute from the below data from date column. Is there any MySQL query to perform the operation? 

<a href="http://i.imgur.com/Z9i4u3x.png" rel="nofollow">http://i.imgur.com/Z9i4u3x.png</a>

MYSQL- Fetch one record per minute

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有所附图片的记录。最后一列有收到的纬度和经度日期和时间。现在，我想从下面的date列中选择每分钟一条记录。是否有任何MySQL查询来执行操作？

问MYSQL-每分钟取一条记录
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MYSQL-每分钟取一条记录EN