mysql group by隐藏,mysql-group by子句列名而不在选择列表中选择

select person

from person

inner join collectionmember

on sourceobjectid=personid

group by sourceobjectid

having count(sourceobjectid)>1;

据说如果使用group by子句,则select列列表中必须存在group by列名,但是上面的查询没有选择sourceobjectid.

解决方法:

结果不是遇到的第一行. MySQL非常清楚所谓“隐藏列”的使用.引用documentation：

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标准一致.

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

标签：sql,mysql

来源： https://codeday.me/bug/20191122/2062987.html