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

当select子句中不存在聚合函数时,按行为分组

mysql sql

Harish Shetty · 技术社区 · 15 年前

我有一张桌子 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

服务器基于什么决定返回吉尔和弗雷德,排除杰克和汤姆?

我在mysql中运行这个查询。

注1:我知道查询本身没有意义。我正在尝试用“分组依据”方案调试问题。为此,我试图理解默认行为。

注意2:我习惯于编写与group by子句相同的select子句(减去聚合字段)。当我遇到上面描述的行为时,我开始想,我是否可以在以下情况下依赖它: 从EMP表中选择薪资在部门中最低/最高的行。例如:这样的SQL语句在MySQL上工作:

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)

8 回复 | 直到 15 年前

mjv 15 年前

读 MySQL documentation 在这一点上。

总之,MySQL允许从group by中省略一些列,但是出于性能考虑。这只是工作 如果省略的列都具有相同的值 (分组内),否则, 查询返回的值确实不确定 正如本帖中其他人正确猜测的那样。为了确保添加ORDERBY子句不会重新引入任何形式的确定性行为。

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

摘自MySQL5.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 13 年前

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

group 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 Madhuri Patel 5 年前

据我所知,出于您的目的,返回的特定行可以被认为是随机的。

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

cube.head 13 年前

你可以把A:

设置sql_mode='仅_full_group_by'

在执行SQL标准按行为分组的查询之前

Rob Farley 15 年前

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

相反,考虑您的查询为:

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

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

希望这有帮助…

Petruza 15 年前

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

-1

Marius 15 年前

尝试使用order by选择所需的行。

SELECT * FROM emp GROUP BY dept ORDER BY name ASC;

将返回以下内容:

name   dept    salary
-----  -----   -----
jack   a       2
fred   b       1

-1

CSharpAtl 15 年前

如果您是按部门分组的,那么其他数据有关系吗?我知道SQL Server甚至不允许这个查询。如果有这种可能性,听起来可能还有其他问题。