使用 Group BY 通过一个 MySQL 查询获取最小值和最大值 [英] Get Min and Max values with one MySQL query with Group BY

问题描述

我之前在这里看到过类似的问题，但他们似乎都在谈论只获得 Min 或 Max 值.我需要同时获得两个，最好是一个查询.我无法理解它在 MySQL 中是如何工作的逻辑.
示例数据

I've seen similar questions asked here before, but they all seem to be talking about getting only Min or Max value. I need to get both, ideally with one query. I can't get my head around the logic how this works in MySQL.
Example data

+--------+--------+---------+---------+----------+
|temp_min|temp_max|pressure |condition|dt_txt    |
+--------+--------+---------+---------+----------+
|9.27    |9.27    |1021.98  |Rain     |2018-05-15|
|8.77    |8.77    |1021.22  |Rain     |2018-05-15|
|9.99    |9.99    |1021.31  |Clear    |2018-05-15|
|13.86   |13.86   |1021.41  |Clouds   |2018-05-15|
|12.39   |12.39   |1019.71  |Rain     |2018-05-14|
|13.42   |13.42   |1020.24  |Rain     |2018-05-14|
|14.14   |14.14   |1020.41  |Rain     |2018-05-14|
|9.01    |9.01    |1018.12  |Rain     |2018-05-14|
|13.94   |13.94   |1020.73  |Rain     |2018-05-14|
|5.64    |5.64    |1018.42  |Clouds   |2018-05-14|
|10.65   |10.65   |1021.8   |Clouds   |2018-05-14|
|8.69    |8.69    |1018.91  |Clouds   |2018-05-14|
|...     |...     |...      |...      |...       |
+--------+--------+---------+---------+----------+

逻辑和常识会决定这样的事情:

Logic and common sense would dictate something like this:

SELECT 
    MIN(`temp_min`) AS `temp_min`,
    MAX(`temp_max`) AS `temp_max`,
    `dt_txt`,
    DAYNAME(`dt_txt`) AS `dayname`,
    `pressure`,
    `condition`,
    `dt_txt`
FROM
    infoboard.forecasts
WHERE `dt_txt` >= CURDATE()
GROUP BY `dt_txt`
ORDER BY `dt_txt` ASC;

结果是:

+--------+--------+---------+---------+----------+--------+
|temp_min|temp_max|pressure |condition|dt_txt    |        |
+--------+--------+---------+---------+----------+--------+
|10.65   |9.01    |1018.12  |Rain     |2018-05-14|Monday  |
|13.86   |9.99    |1021.98  |Rain     |2018-05-15|Tuesday |
+--------+--------+---------+---------+----------+--------+

对于 2018-05-14 最小值应为 8.69，最大值应为 14.14
对于 2018-05-15 最小值应为 8.77 最大值应为 13.42

For 2018-05-14 min should be 8.69 and max should be 14.14
For 2018-05-15 min should be 8.77 and max should be 13.42

如何从 temp_* 列中获取真实的最小值和最大值以及驱动正确查询的逻辑是什么?

How do I get real min and max values from temp_* columns and what's the logic that drives the correct query?

推荐答案

您似乎将数字值存储为字符串.你真的应该修复数据.但是，您可以修复查询.在我看来，最简单的方法是隐式转换:

You appear to be storing numeric values as strings. You really should fix the data. But, you can fix the query. In my opinion, the simplest method is implicit conversion:

SELECT MIN(`temp_min` + 0) AS `temp_min`,
       MAX(`temp_max` + 0) AS `temp_max`,
       `dt_txt`, DAYNAME(`dt_txt`) AS `dayname`,
       `pressure`, `condition`, `dt_txt`
FROM infoboard.forecasts
WHERE `dt_txt` >= CURDATE()
GROUP BY `dt_txt`
ORDER BY `dt_txt` ASC;

请注意，pressure 和 condition 不在您的 GROUP BY 中，因此可以从任意行中选择值.这是一个非常糟糕的做法，意味着您的查询在几乎任何其他数据库中都不起作用.

Note that pressure and condition are not in your GROUP BY, so values are chosen from arbitrary rows. This is a really bad practice, and means that your query would not work in almost any other database.

您可以通过执行以下操作来修复数据:

You can fix the data by doing something like:

alter table infoboard.forecasts
    modify column temp_min decimal(6, 3),
    modify column temp_max decimal(6, 3);

我怀疑你也想对 pressure 做同样的事情.

I suspect you would want to do the same for pressure as well.

这篇关于使用 Group BY 通过一个 MySQL 查询获取最小值和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！