如何显示MySQL中的每个最大值? [英] How to show every max value in mysql?

问题描述

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

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

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

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).

推荐答案

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

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

要澄清: 我找到的值大于该行中的下一个值. 为了获得最终值，我在子查询周围添加了IFNULL，以确保子查询随后将返回-1

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

这篇关于如何显示MySQL中的每个最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！