我可以使用group by的非聚合列吗？ [英] Can I use non-aggregate columns with group by?

问题描述

您不能（不应该）将非集合放在 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;

输出：

That outputs this:

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 away 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.

推荐答案

您无法获得Id MAX找到的行，因为可能不会有最大年龄的一个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.

这篇关于我可以使用group by的非聚合列吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！