MySQL 从 GROUP BY 中获取具有最高或最低值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16910050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get row with highest or lowest value from a GROUP BY
提问by Pablo Fernandez heelhook
I'm trying to get the row with the highest/lowest number, after performing a GROUP BY:
在执行以下操作后，我正在尝试获取具有最高/最低编号的行GROUP BY：
Here is my test data
这是我的测试数据
mysql> SELECT * FROM test;
+----+-------+------+
| id | value | name |
+----+-------+------+
| 1 | 10 | row1 |
| 2 | 12 | row2 |
| 3 | 10 | row2 |
| 4 | 5 | row2 |
+----+-------+------+
4 rows in set (0.00 sec)
To get the lowest value, I'll use MIN()
为了获得最低值，我将使用 MIN()
mysql> SELECT id, name, MIN(value) AS value FROM test GROUP BY name;
+----+------+-------+
| id | name | value |
+----+------+-------+
| 1 | row1 | 10 |
| 2 | row2 | 5 |
+----+------+-------+
2 rows in set (0.00 sec)
Now, the id row2is 2, but it should be 4.
现在， idrow2是2，但它应该是4。
I also tried with a join:
我也尝试过加入：
mysql> SELECT t1.* FROM
(SELECT id, name, MIN(value) AS value
FROM test GROUP BY name) AS t1
INNER JOIN test AS t2 ON t1.id = t2.id;
+----+------+-------+
| id | name | value |
+----+------+-------+
| 1 | row1 | 10 |
| 2 | row2 | 5 |
+----+------+-------+
2 rows in set (0.00 sec)
How can I get the correct ID for each result based on what the lowest valueis?
如何根据最低value值获得每个结果的正确 ID ？
回答by hims056
I think this is what you are trying to achieve:
我认为这就是您要实现的目标：
SELECT t.* FROM test t
JOIN
( SELECT Name, MIN(Value) minVal
FROM test GROUP BY Name
) t2
ON t.Value = t2.minVal AND t.Name = t2.Name;
Output:
输出：
╔════╦═══════╦══════╗
║ ID ║ VALUE ║ NAME ║
╠════╬═══════╬══════╣
║ 1 ║ 10 ║ row1 ║
║ 4 ║ 5 ║ row2 ║
╚════╩═══════╩══════╝
See this SQLFiddle
看到这个 SQLFiddle
Here I have self-joined the table with minVal and Name.
在这里，我使用 minVal 和 Name 自行加入了该表。
