代码之家 › 专栏 › 技术社区 › Harish Shetty

SELECT子句中不存在聚合函数时的GROUP BY行为

mysql sql

Harish Shetty · 技术社区 · 16 年前

我有一张桌子 emp

name   dept    salary
-----  -----   -----
Jack   a       2
Jill   a       1
Tom    b       2
Fred   b       1

当我执行以下SQL时:

SELECT * FROM emp GROUP BY dept

我得到以下结果:

name   dept    salary
-----  -----   -----
Jill   a       1
Fred   b       1

注2:我习惯于编写与GROUP BY子句相同的SELECT子句(减去聚合字段)。当我遇到上述行为时,我开始想我是否可以在以下场景中依赖它:

SELECT A.*, MIN(A.salary) AS min_salary FROM emp AS A GROUP BY A.dept

我没有找到任何材料来描述这种SQL为什么有效,更重要的是,如果我能始终如一地依赖这种行为。如果这是一个可靠的行为,那么我可以避免以下查询:

SELECT A.* FROM emp AS A WHERE A.salary = ( 
            SELECT MAX(B.salary) FROM emp B WHERE B.dept = A.dept)

9 回复 | 直到 13 年前

mjv 16 年前

阅读 MySQL documentation 在这一点上。

简而言之,MySQL允许从GROUP BY中省略一些列,但出于性能考虑这仅适用于 如果省略的列都具有相同的值 (在一个分组内),否则, 查询返回的值确实是不确定的 ,正如其他人在这篇文章中正确猜测的那样。可以肯定的是,添加ORDER BY子句不会重新引入任何形式的确定性行为。

虽然不是问题的核心,但这个例子表明,使用*而不是显式枚举所需的列通常是一个坏主意。

MySQL 5.0文档摘录:

When using this feature, all rows in each group should have the same values
for the columns that are omitted from the GROUP BY part. The server is free
to return any value from the group, so the results are indeterminate unless
all values are the same.

Samuel Hodge 15 年前

这有点晚了,但我会把它提出来供将来参考。

GROUP BY取第一行有重复项,并丢弃结果集中与之匹配的任何行。因此,如果Jack和Tom属于同一部门,那么在普通SELECT中首先出现的人将是GROUP BY中的结果行。

如果你想控制列表中首先出现的内容,你需要执行ORDER BY。但是,SQL不允许ORDER BY出现在GROUP BY之前,因为它会抛出异常。解决此问题的最佳方法是在子查询中执行ORDER BY,然后在外部查询中执行GROUP BY。这里有一个例子:

SELECT * FROM (SELECT * FROM emp ORDER BY name) as foo GROUP BY dept

这是我发现的最好的技术。我希望这能帮助别人。

Nico Haase 7 年前

据我所知,为了您的目的,返回的特定行可以被视为随机的。

订购仅在以下时间进行 GROUP BY 完成了

cube.head 14 年前

你可以放一个:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

在执行SQL标准之前 GROUP BY 行为

Rob Farley 16 年前

我发现最好的办法是认为这种类型的查询不受支持。在大多数其他数据库系统中,您不能在HAVING、SELECT或ORDER BY子句中包含既不在GROUP BY子句中也不在聚合函数中的列。

相反,请考虑您的查询内容为:

SELECT ANY(name), dept, ANY(salary)
FROM emp 
GROUP BY dept;

…因为这就是正在发生的事情。

希望这能有所帮助。...

Petruza 16 年前

我认为ANSI SQL要求select只包含GROUP BY子句中的字段,以及聚合函数。 MySQL的这种行为看起来像是返回一些行,可能是服务器读取的最后一行,也可能是它手头的任何一行,但不要依赖它。

-1

Marius 16 年前

这将为每个人选择最近的一行:

SELECT * FROM emp
WHERE ID IN
(
    SELECT
        MAX(ID) AS ID
    FROM
        emp
    GROUP BY
        name
)

-1

CSharpAtl 16 年前

如果按部门分组,其他数据是否重要?我知道Sql Server甚至不允许此查询。如果有这种可能性,听起来可能还有其他问题。