MySQL MAX函数混合行

Question

我有一个查询SELECT id, MAX(value) FROM table1，它返回正确的值，但它采用表的第一个id，而不是与返回值对应的id (id是主键)。我已经看到了解决方案，但它们都需要一个WHERE子句，而我不能在我的例子中使用它。

Answer 1

我相信您要做的是返回具有最大值的行的id。是那么回事吗？

我很好奇为什么你不能使用WHERE子句？

但是好的，使用这个约束就可以解决这个问题。我将假设您的表在id上是惟一的(如果不是，您真的应该与构建它的人谈谈，并询问为什么?)

SELECT id, value 
FROM table1 
ORDER BY value DESC 
LIMIT 1

这将按照值的降序(greatest -> least)对表进行排序，然后只显示第一行(即具有最大“值”的行)。

如果您的表在id上不是唯一的，您仍然可以按ID分组并获得相同的值

SELECT id, max(value) as max_value
FROM table1
GROUP BY id
ORDER BY max_value DESC
LIMIT 1

Answer 2

首先，为了回答为什么您的查询表现为您观察到的方式:我怀疑您运行时没有使用sql_mode = only_full_group_by，因为您的查询很可能是generate an error otherwise。正如您已经注意到的，这可能会导致一些奇怪的结果。

如果禁用ONLY_FULL_GROUP_BY，则对GROUP BY的标准SQL用法的MySQL扩展允许select list、HAVING condition或ORDER BY列表引用非聚合列，即使这些列在功能上不依赖于GROUP BY列。这会导致MySQL接受前面的查询。在这种情况下，服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的，这可能不是您想要的。

在本例中，由于没有GROUP BY子句，因此整个表实际上就是组。

要获得一个与表中最大值相关联的id，您可以选择所有行，按值排序(降序)，然后仅限于第一个结果，不需要聚合运算符(或WHERE caluse)：

SELECT id, value FROM table1 ORDER BY value DESC LIMIT 1

请注意，如果有多个if具有(相同)最大值，则只返回其中一个。@RaymondNijland在评论中指出，每次你运行它时，这可能会给出不同的结果(对于id，value总是最大值)，你也可以通过按id排序来使其更具确定性：

SELECT id, value FROM table1 ORDER BY value DESC, id ASC LIMIT 1

同样，如果由于某种原因同一个ID有多个值，那么如果它的某一行恰好是最大值，它仍然会返回该ID --幸运的是，这种情况不适用于这种情况，因为您提到id是主键。

Answer 3

我想您忘记了group by子句：

SELECT id, MAX(value) FROM table1 GROUP BY id

编辑:为了满足您的需求，您可以这样做

SELECT id, MAX(value) 
FROM table1 
GROUP BY id
HAVING MAX(value) = (SELECT MAX(value) FROM table1)

如果您有多个ids的最大值，这可能会给您提供多个结果。在这种情况下，你可以添加"LIMIT 1“来只得到一个结果，但这将是非常奇怪和随机的。