相邻列中同一行中 MIN MAX 的每日 DATETIME [英] Daily DATETIME of MIN MAX in same row in an adjacent column

问题描述

为我编写的这个查询寻找一种方法来显示 MIN & 的实际 DATETIME当天的最高温度出现在相邻列的同一行中.

Looking for a way for this query that I have written to display the actual DATETIME that the MIN & MAX temperature occurred at for that day in the same row in an adjacent column.

我一直在搜索和尝试一些东西，但我对连接有点迷茫，我认为您需要为此使用它.

I have been searching and trying a few things, but I am a bit lost with joins, which I assume you need to use for this.

当前查询和结果..

SELECT 
    YEAR(tstLocal) AS "YEAR", 
    MONTHNAME(tstLocal) AS "MONTH", 
    DAY(tstLocal) AS "DAY",
    min(tempC) AS "DAILY MIN TEMP", 
    max(tempC) AS "DAILY MAX TEMP",  
    ROUND(AVG(tempC),1) AS "DAILY AVG TEMP"
FROM 
    house.outside1
WHERE 
    YEAR(tstLocal) = '2014' AND MONTH(tstLocal) = '7'
GROUP BY 
    YEAR(tstLocal),
    MONTH(tstLocal),
    DAY(tstLocal);

我需要怎么做才能显示这些最低/最高温度发生的日期时间?

What do I need to do to display it with the DATETIME that these MIN / MAX temperatures occurred at?

推荐答案

因为您已经在使用 group by，group_concat()/substring_index() 技巧会起作用:

Because you are already using a group by, the group_concat()/substring_index() trick will work:

SELECT YEAR(tstLocal) AS "YEAR", MONTHNAME(tstLocal) AS "MONTH", DAY(tstLocal) AS "DAY",
       min(tempC) AS "DAILY MIN TEMP", max(tempC) AS "DAILY MAX TEMP",
       ROUND(AVG(tempC),1) AS "DAILY AVG TEMP",
       substring_index(group_concat(tstlocal order by tempC asc), ',', 1) as min_datetime,
       substring_index(group_concat(tstlocal order by tempC desc), ',', 1) as max_datetime
FROM house.outside1
WHERE  YEAR(tstLocal) = '2014' AND MONTH(tstLocal) = '7'
GROUP BY YEAR(tstLocal), MONTH(tstLocal),  DAY(tstLocal);

这篇关于相邻列中同一行中 MIN MAX 的每日 DATETIME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！