相邻列中同一行中 MIN MAX 的每日 DATETIME

本文介绍了相邻列中同一行中 MIN MAX 的每日 DATETIME的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

为我编写的这个查询寻找一种方法来显示 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);