按子句列名分组，而不在选择列表中选择 [英] group by clause column name without selecting it in select list

问题描述

select person
from person
inner join collectionmember
    on sourceobjectid=personid
group by sourceobjectid 
having count(sourceobjectid)>1;

据说如果使用group by子句，则按列名必须在选择列列表中存在，但是上面的查询在不选择sourceobjectid的情况下有效.

It is said that if we use group by clause the group by column name must be there in select column list,but above query works without selecting sourceobjectid.How this query works?

推荐答案

结果不是遇到的第一行. MySQL非常清楚所谓的隐藏列"的使用.引用文档:

The results is not the first row encountered. MySQL is quite clear about the use of so-called "hidden columns". To quote from the documentation:

MySQL扩展了GROUP BY的使用，以便选择列表可以引用 未在GROUP BY子句中命名的非聚合列.这表示 前面的查询在MySQL中是合法的.您可以使用此功能 通过避免不必要的列排序和获得更好的性能 分组.但是，这主要是有用的，当每个中的所有值 在GROUP BY中未命名的非聚合列对于每个列均相同 团体.服务器可以从每个组中自由选择任何值，因此 除非它们相同，否则选择的值是不确定的. 此外，不能从每个组中选择值 受添加ORDER BY子句影响.结果集排序 在选择值之后发生，并且ORDER BY不会影响 服务器在每个组中选择哪个值.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY 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 GROUP BY 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 ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

此扩展的目的是允许查询在您对表的主键进行分组的位置，而不必放在所有其他列中.这种行为实际上与ANSI标准一致.

The intention of this extension is to allow queries where you group on the primary key of a table, and not have to put in all the additional columns. This behavior is actually consistent with the ANSI standard.

在其他情况下，可能会使用隐藏列".选择的值是任意. MySQL甚至不保证它们来自同一行(尽管实际上是这样).

The use of "hidden columns" in other cases is suspect. The values chosen are arbitrary. MySQL does not even guarantee that they come from the same row (although in practice, they do).

这篇关于按子句列名分组，而不在选择列表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！