mysql - 在两列中使用 MAX() 进行 GROUP BY

Question

假设我有一个表 test 如下:

我想筛选在 number2 中具有最大值的行，然后在相同名称中筛选在 number1 中具有最大值的行。

因此，上表中的预期结果将是第 2 行和第 4 行:

2    cuong    7    10
4    nam      3    8

选择第 2 行是因为它的 number2 是 10(最大值)并且 number1 是 7(5 和 7 中的最大值)。与第 4 行类似。

可以通过以下方式获取结果:

SELECT id, name, MAX(number1), number2
FROM test
WHERE number2 IN (select max(number2) from test group by `name`)
group by `name`;

但是这个解决方案是针对我上面的假设表，在我的实际问题中，它很复杂并且必须将很多表连接在一起，WHERE子句中的子查询使得性能非常非常慢。

所以，我想找到一个更简单的解决方案，它不在 WHERE 子句中使用子查询，只是只有GROUP BY，我也试过:

SELECT id, name, MAX(number1), number2
FROM cuong_test.test
GROUP BY `name`
HAVING number2 = MAX(number2);

但是没有成功。

Answer 1

使用 not exists 最容易做到这一点以获得最大数量:

SELECT id, name, number1, number2
FROM test t
WHERE NOT EXISTS (select 1
                  from test t2
                  where t2.name = t.name and
                        (t2.number2 > t.number2 or
                         t2.number2 = t.number2 and
                         t2.number1 > t.number1
                        )
                 );

在 MySQL 中，您还可以使用 group_concat() 魔术来完成此操作:

select id, name,
       substring_index(group_concat(number1 order by number2 desc), ',', 1) as number1,
       max(number2) as number2
from test t
group by name;