MySQL Handling of GROUP BY--官方文档

For the query to be legal,the??column must be omitted from the select list or named in the??clause.

?so that the select list can refer to nonaggregated columns not named in the??clause. This means that the preceding query is legal in MySQL. 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??are the same for each group. The server is free to choose any value from each group,so unless they are the same,the values chosen are indeterminate. Furthermore,the selection of values from each group cannot be influenced by adding an??clause. Sorting of the result set occurs after values have been chosen,and??does not affect which values within each group the server chooses.

A similar MySQL extension applies to the??clause. In standard SQL,a query that includes a?clause cannot refer to nonaggregated columns in the??clause that are not named in the??clause. A MySQL extension permits references to such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise,the result is indeterminate.

To disable the MySQL??extension,enable the??SQL mode. This enables standard SQL behavior: Columns not named in the??clause cannot be used in the select list or??clause unless enclosed in an aggregate function.

?also affects use of aliases in the??clauses. For example,the following query returns?values that occur only once in table?:

MySQL extends this behavior to permit the use of an alias in the??clause for the aggregated column:

Enabling??disables this MySQL extension and a??error occurs because the column??in the??clause is not enclosed in an aggregate function (instead,it?is?an aggregate function).

The select list extension also applies to?. That is,you can refer to nonaggregated columns in the??clause that do not appear in the??clause. (However,as mentioned previously,??does not affect which values are chosen from nonaggregated columns; it only sorts them after they have been chosen.) This extension does not apply if the??SQL mode is enabled.

In some cases,you can use??and??to obtain a specific column value even if it is not unique. If the?column contains integers no larger than 6 digits,the following query gives the value of??from the row containing the smallest??value:

See?.

If you are trying to follow standard SQL,you cannot use expressions in??clauses. As a workaround,use an alias for the expression:

tbl_name
  GROUP BY id,val;

MySQL permits expressions in??clauses,so the alias is unnecessary:

tbl_name
  GROUP BY id,FLOOR(value/100);




?http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!