我可以将非聚合列与 group by 一起使用吗?

2021-12-27 00:00:00 sql group-by aggregate mysql

您不能(不应该)在 GROUP BY 查询的 SELECT 行中放置非聚合.

You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query.

然而，我想访问与最大值关联的非聚合之一.用简单的英语，我想要一个表，其中包含各种最旧的 ID.

I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table with the oldest id of each kind.

CREATE TABLE stuff (
   id int,
   kind int,
   age int
);

这个查询给了我我想要的信息:

This query gives me the information I'm after:

SELECT kind, MAX(age)
FROM stuff
GROUP BY kind;

但这并不是最有用的形式.我真的想要与每一行相关联的 id 以便我可以在以后的查询中使用它.

But it's not in the most useful form. I really want the id associated with each row so I can use it in later queries.

我正在寻找这样的东西:

I'm looking for something like this:

SELECT id, kind, MAX(age)
FROM stuff
GROUP BY kind;

输出这个:

SELECT stuff.*
FROM
   stuff,
   ( SELECT kind, MAX(age)
     FROM stuff
     GROUP BY kind) maxes
WHERE
   stuff.kind = maxes.kind AND
   stuff.age = maxes.age

似乎真的应该有一种无需加入即可获取此信息的方法.我只需要 SQL 引擎在计算最大值时记住其他列.

It really seems like there should be a way to get this information without needing to join. I just need the SQL engine to remember the other columns when it's calculating the max.

推荐答案

无法获取 MAX 找到的行的 id，因为最长年龄的 id 可能不止一个.

You can't get the Id of the row that MAX found, because there might not be only one id with the maximum age.

相关文章