blocks|key|3098348|text|我认为它是为了处理按一个字段分组意味着其他字段也在分组的情况：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3098349|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|code-block|syntax|javascript|3098350|在这种情况下，每个user.id的user.name都是唯一的，所以在GROUP+BY子句中不需要user.name是很方便的(尽管，正如您所说的，存在一定的问题空间)|offset|length|style|CODE|3098351|entityMap^0|0|0|Z|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I believe that it was to handle the case where grouping by one field would imply other fields are also being grouped:

<pre><code>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
</code></pre>

In this case the user.name will always be unique per user.id, so there is convenience in not requiring the user.name in the <code>GROUP BY</code> clause (although, as you say, there is definite scope for problems)

blocks|key|334275|text|不幸的是，几乎所有的SQL变体都有违反ANSI的情况，并产生不可预知的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334276|在我看来，他们的意图是将其视为许多其他系统具有的"FIRST(Y)“函数。|334277|更有可能的是，这种构造让MySQL团队感到遗憾，但又不想因为应用程序的数量而停止支持。|334278|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Unfortunately almost all the SQL varieties have situations where they break ANSI and have unpredictable results.
It sounds to me like they intended it to be treated like the &quot;FIRST(Y)&quot; function that many other systems have.
More than likely, this construct is something that the MySQL team regret, but don't want to stop supporting because of the number of applications that would break.

blocks|key|3311218|text|根据this+page+(+5.0在线手册)的说法，它是为了更好的性能和用户便利性。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3311219|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html^0|2|9|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

According to <a href="https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html" rel="noreferrer">this page</a> (the 5.0 online manual), it's for better performance and user convenience.

blocks|key|334284|text|当您使用不带聚合函数的GROUP+BY时，MySQL认为这是一个单独的列。使用其他选项，你要么让整个结果是不同的，要么必须使用子查询，等等。问题是结果是否真正可预测。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334285|此外，好的信息在this+thread中。|offset|length|334286|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/2197693/any-reason-for-group-by-clause-without-aggregation-function^0|0|8|B|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

MySQL treats this is a single column DISTINCT when you use GROUP BY without an aggregate function. Using other options you either have the whole result be distinct, or have to use subqueries, etc. The question is whether the results are truly predictable.

Also, good info is in <a href="https://stackoverflow.com/questions/2197693/any-reason-for-group-by-clause-without-aggregation-function">this thread</a>.

blocks|key|335385|text|它实际上是一个非常有用的工具，当你按一个字段分组时，所有其他字段都不必在一个聚合函数中。您可以操作将返回的结果，只需先对其进行排序，然后对其进行分组。例如，如果我想要获取用户登录信息，并且想要查看用户最后一次登录的时间，我会这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|335386|表格|335387|USER
user_id+%7C+name

USER_LOGIN_HISTORY+
user_id+%7C+date_logged_in|code-block|syntax|javascript|335388|对于一个用户，USER_LOGIN_HISTORY有多个行，所以如果我将用户加入其中，它将返回许多行。因为我只对最后一个条目感兴趣，所以我会这样做|335389|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|335390|这将返回一行，其中包含用户名和该用户最后一次登录的时间。|335391|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Its actually a very useful tool that all other fields dont have to be in an aggregate function when you group by a field. You can manipulate the result which will be returned by simply ordering it first and then grouping it after. for instance if i wanted to get user login information and i wanted to see the last time the user logged in i would do this.

Tables

<pre><code>USER
user_id | name

USER_LOGIN_HISTORY 
user_id | date_logged_in
</code></pre>

USER_LOGIN_HISTORY has multiple rows for one user so if i joined users to it it would return many rows. as i am only interested in the last entry i would do this

<pre><code>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
</code></pre>

This would return one row with the name of the user and the last time that user logged in.

blocks|key|337702|text|我在mysql参考页面上读到：“您可以使用此功能，通过避免不必要的列排序和分组来获得更好的性能。但是，这主要是在每个组中没有在GROUP+by中命名的每个非聚集列中的所有值都相同时有用的。”|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337703|我建议你阅读这个页面(链接到mysql的参考手册)：http://dev.mysql.com/doc/refman/5.5/en//group-by-extensions.html|offset|length|337704|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en//group-by-extensions.html^0|0|Q|1S|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

From what I have read in the mysql reference page, it says:
"You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group."

I suggest you to read this page (link to the reference manual of mysql): 
<a href="http://dev.mysql.com/doc/refman/5.5/en//group-by-extensions.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en//group-by-extensions.html</a>

Surprise -- this is a perfectly valid query in MySQL:

<pre><code>select X, Y from someTable group by X
</code></pre>

If you tried this query in Oracle or SQL Server, you’d get the natural error message:

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

So how does MySQL determine which Y to show for each X? It just picks one. From what I can tell, it just picks the first Y it finds. The rationale being, if Y is neither an aggregate function nor in the group by clause, then specifying “select Y” in your query makes no sense to begin with. Therefore, I as the database engine will return whatever I want, and you’ll like it.

There’s even a MySQL configuration parameter to turn off this “looseness”.
<a href="http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by" rel="noreferrer">http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by</a>

This article even mentions how MySQL has been criticized for being ANSI-SQL non-compliant in this regard.
<a href="http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html" rel="noreferrer">http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html</a>

My question is: Why was MySQL designed this way? What was their rationale for breaking with ANSI-SQL?

Why does MySQL allow "group by" queries WITHOUT aggregate functions?

SQL Server

数据库

MySQL

令人惊讶的是--这是一个在MySQL中非常有效的查询：select X, Y from someTable group by X如果您在Oracle或SQL Server中尝试此查询，您将得到自然错误消息：Column 'Y' is invalid in the select list because it is no...

问为什么MySQL允许没有聚合函数的"group by“查询？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么MySQL允许没有聚合函数的"group by“查询？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么MySQL允许没有聚合函数的"group by“查询？
EN