mariadb/mysql在分组中查找具有最大值和最小值的行字段

nwlqm0z1 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(385)

我有这个问题。返回每日最低和最高温度：

select  year(DateTimeS),month(DateTimeS),day(DateTimeS),min(Low),max(High) from temperature
where year(DateTimeS) = 2018
group by year(DateTimeS),month(DateTimeS),day(DateTimeS)

我在这个查询中缺少两个字段，lowtime和maxtime。我不知道如何得到最小值（低）和最大值（高）发生的时间(datetimes是一个datetime字段，其余的都是十进制的）该表有如下逐分钟的温度数据：

+-----------------------+--------+-------+
|      "DateTimeS"      | "High" | "Low" |
+-----------------------+--------+-------+
| "2018-09-07 23:58:00" | "89"   | "87"  |
| "2018-09-07 23:57:00" | "88"   | "85"  |
| "2018-09-07 23:56:00" | "86"   | "82"  |
|        .              |        |       |
|        etc...         |        |       |
+-----------------------+--------+-------+

有什么想法吗？

sql mysql mariadb groupwise-maximum

来源：https://stackoverflow.com/questions/52527625/mariadb-mysql-find-row-fields-with-max-and-min-values-in-a-grouping

1条答案

按热度按时间

oyjwcjzk1#

在mariadb 10.3中，您应该能够使用窗口函数。所以：

select year(DateTimeS), month(DateTimeS), day(DateTimeS),
       min(Low), max(High),
       max(case when seqnum_l = 1 then DateTimeS end) as dateTimeS_at_low,
       max(case when seqnum_h = 1 then DateTimeS end) as dateTimeS_at_high
from (select t.*,
             row_number() over (partition by date(DateTimeS) order by low) as seqnum_l,
             row_number() over (partition by date(DateTimeS) order by high desc) as seqnum_h
      from temperature t
     ) t
where year(DateTimeS) = 2018
group by year(DateTimeS), month(DateTimeS), day(DateTimeS);

赞(0）回复(0）举报 2021-06-18

我来回答

mariadb/mysql在分组中查找具有最大值和最小值的行字段

1条答案

相关问题

热门标签

最新问答