如何显示mysql中的每个最大值？

发布于 2024-11-07 20:45:59 字数 668 浏览 0 评论 0原文

我有多个具有不同时间戳的值，如下所示：

10   01:01:00
20   01:35:00
30   02:10:00
05   02:45:00
12   03:05:00
21   03:30:00
10   04:06:00
40   05:15:00

我没有可以用来分组并查找最大值的列。我想获取最大值为 30,21 和 40 的记录。数据始终采用这种格式，就像值递增，然后再次从零开始。什么查询可以帮助我找到这些记录？

为了澄清，它是按时间戳排序的，我想获取局部最大值的时间戳，即下一行具有较小值的行：

value    tmstmp
-----   --------
 10     01:01:00
 20     01:35:00
 30     02:10:00  <-- this one since next value is 5 (< 30).

 05     02:45:00
 12     03:05:00
 21     03:30:00  <-- this one since next value is 10 (< 21).

 10     04:06:00
 40     05:15:00  <-- this one since next value is 40 (< infinity).

原文

I've multiple values with different timestamps like the following:

10   01:01:00
20   01:35:00
30   02:10:00
05   02:45:00
12   03:05:00
21   03:30:00
10   04:06:00
40   05:15:00

I don't have a column with which I can group by and find max. I want to get the records with max values like 30,21, and 40. The data is always in this format, like value increasing and then starts from zero again. What query will help me to find these records?

To clarify, it's sorted by the timestamp, and I want to get the timestamps for the local maxima, the rows where the next row has a lesser value:

value    tmstmp
-----   --------
 10     01:01:00
 20     01:35:00
 30     02:10:00  <-- this one since next value is 5 (< 30).

 05     02:45:00
 12     03:05:00
 21     03:30:00  <-- this one since next value is 10 (< 21).

 10     04:06:00
 40     05:15:00  <-- this one since next value is 40 (< infinity).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏末染殇 2024-11-14 20:45:59

不知怎的，我不清楚你的问题。
假设第一列名称是“value”，第二列名称是“timestamp”。

从按时间戳分组中选择最大值（值）。

回复收藏 0 原文

红焚 2024-11-14 20:45:59

这个答案可能有点晚了，但我想我已经找到了解决方案

SELECT * FROM temp t1 WHERE value > 
  IFNULL(
    (SELECT value FROM temp t2
    WHERE t2.tmstmp > t1.tmstmp ORDER BY t2.tmstmp ASC limit 1),
   -1
  ) 
ORDER BY tmstmp ASC

来澄清：
我找到了大于行中下一个值的值。
为了也获得最终值，我在子查询周围添加了 IFNULL 以确保子查询将返回 -1

我看到的唯一问题是当时间转到第二天时，这就是为什么我希望你可以附加一个日期也对它。

希望这仍然能帮助其他人

This answer might be a bit late, however i think i have found the solution

SELECT * FROM temp t1 WHERE value > 
  IFNULL(
    (SELECT value FROM temp t2
    WHERE t2.tmstmp > t1.tmstmp ORDER BY t2.tmstmp ASC limit 1),
   -1
  ) 
ORDER BY tmstmp ASC

To clarify:
I find the values where the value is greater than the next value in the row.
To also get the final value I have added an IFNULL around the subquery to make sure the subquery will then return -1

The only problem i see is when the time goes over to the next day, that's why i hope you can have a date appended to it as well.

Hopefully this will still help others

回复收藏 0 原文

~没有更多了~