代码之家 › 专栏 › 技术社区 › Aaron Fi

为什么MySQL允许不使用聚合函数的“group by”查询?

mysql sql

Aaron Fi · 技术社区 · 15 年前

惊喜——这是MySQL中一个完全有效的查询:

select X, Y from someTable group by X

如果您在Oracle或SQL Server中尝试此查询,则会得到自然错误消息:

Column 'Y' is invalid in the select list because it is not contained in 
either an aggregate function or the GROUP BY clause.

那么,MySQL如何确定每个X显示哪个Y呢?它只选了一个。据我所知,它只选择它找到的第一个Y。如果y既不是聚合函数,也不是group by子句,那么在查询中指定__select y_157;就没有意义了。因此,我作为数据库引擎将返回我想要的任何内容,您会喜欢的。

甚至有一个mysql配置参数来关闭这个松动。 http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

本文甚至提到了MySQL在这方面被批评为不符合ANSI-SQL。 http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html

我的问题是: 为什么? MySQL是这样设计的吗?他们打破ANSI-SQL的理由是什么?

6 回复 | 直到 7 年前

Cebjyre 15 年前

我认为这是为了处理这样一种情况,即按一个字段分组意味着其他字段也将被分组:

SELECT user.id, user.name, COUNT(post.*) AS posts 
FROM user 
  LEFT OUTER JOIN post ON post.owner_id=user.id 
GROUP BY user.id

在这种情况下,user.name对于每个user.id始终是唯一的,因此在 GROUP BY 条款(尽管如你所说,有明确的问题范围)

micha 7 年前

根据 this page (5.0在线手册),这是为了更好的性能和用户方便。

Rob Farley 15 年前

不幸的是,几乎所有的SQL变体都有破坏ANSI的情况,并且会产生不可预知的结果。

在我看来,他们打算把它当作许多其他系统所具有的“第一(y)”功能来对待。

更可能的是,这个构造是MySQL团队遗憾的事情,但不想因为应用程序的数量而停止支持。

抢劫

Community CDub 8 年前

当您使用group by而不使用聚合函数时,mysql认为这是一个单独的列。使用其他选项,您要么让整个结果是不同的,要么必须使用子查询等。问题是结果是否真的是可预测的。

还有,好消息在 this thread .

Milo 12 年前

从我在mysql参考页面上看到的内容来看,它说: “通过避免不必要的列排序和分组,可以使用此功能获得更好的性能。但是,这主要在以下情况下有用:没有在group by中命名的每个非聚合列中的所有值对于每个组都是相同的。”

我建议您阅读本页(链接到MySQL参考手册): http://dev.mysql.com/doc/refman/5.5/en//group-by-extensions.html

-1

Fluffeh 12 年前

它实际上是一个非常有用的工具,当您按字段分组时,所有其他字段都不必在聚合函数中。您可以操作返回的结果,只需先对其排序,然后再对其进行分组即可。例如,如果我想获取用户登录信息,并且我想查看用户上次登录的时间,我会这样做。

桌子

USER
user_id | name

USER_LOGIN_HISTORY 
user_id | date_logged_in

用户登录历史记录对于一个用户有多行,因此如果我加入用户,它将返回多行。因为我只对最后一个条目感兴趣,所以我会这样做的

select 
  user_id,
  name,
  date_logged_in

from(

  select 
    u.user_id, 
    u.name, 
    ulh.date_logged_in

  from users as u

    join user_login_history as ulh
      on u.user_id = ulh.user_id

  where u.user_id = 1234

  order by ulh.date_logged_in desc 

)as table1

group by user_id

这将返回一行,其中包含用户名称和该用户上次登录的时间。