MySQL选择列中具有最低值的行 [英] MySQL Select row with lowest value in column

问题描述

我有一张桌子

+------+-----+-------+
| name | age | class |
+------+-----+-------+
| Ben  |   4 | B     |
| Alex |   7 | A     |
| Jim  |   3 | B     |
| Ben  |   5 | C     |
| Ben  |   2 | C     |
| Alex |   9 | A     |
+------+-----+-------+

我需要一个查询，以便选择年龄最低的人，以便得到:

I need a query so that I can select the person with the lowest age such that I get:

+------+-----+-------+
| name | age | class |
+------+-----+-------+
| Ben  |   2 | C     |
| Jim  |   3 | B     |
| Alex |   7 | A     |
+------+-----+-------+

我一直在使用各种组合或GROUP BY和ORDER BY，但似乎无法正确处理.

I've been messing with various combinations or GROUP BYs and ORDER BYs and can't seem to get it right.

该表还包含约800万条记录，因此性能非常重要.

Also, the table consists of about 8 million records so performance is important.

推荐答案

您首先必须选择每个班级的最低年龄:

You first have to select the minimum age per class:

select min(age) as age, class as class from t group by class

(注意:我假设您希望每个班级的最小年龄.我想要每个名称的最小年龄，然后在查询中将class替换为name)

(Note: I am assuming you want the minimum age per class. I you want the minimum age per name, then replace class with name in the queries ...)

然后，您必须将结果与表连接起来以获取相应的行. 完整的SQL将是

Then you have to join the result with your table to get the respective rows. The full SQL would be

select t.* from t 
inner join
( 
  select min(age) as age, class as class from t group by class
) min_ages on t.age = min_ages.age and t.class = min_ages.class;

为获得最佳性能，请确保同时为age和class(或name，无论您要在group by表达式中使用哪个索引)建立索引.

For optimal performance, make sure that age is indexed as well as class (or name, whichever you want in your group by expression).

这篇关于MySQL选择列中具有最低值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！