每当我们在sql(MIN,MAX,AVG等)中使用聚合函数时,我们必须始终GROUP BY所有非聚合列,例如：
SELECT storeid,storename,SUM(revenue),COUNT(*) FROM Sales GROUP BY storeid,storename
当我们在SELECT语句中使用函数或其他计算时,它变得更加突出,因为这也必须复制到GROUP BY子句.
SELECT (2 * (x + y)) / z + 1,MyFunction(x,y),SUM(z) FROM AnotherTable GROUP BY (2 * (x + y)) / z + 1,y)
如果我们改变了SELECT语句,我们必须记住对GROUP BY子句进行相同的更改.
GROUP BY子句是多余的吗？
>如果确实是这样,那么为什么sql中有一个GROUP BY子句呢？
>如果不是这样,那么GROUP BY给我们什么额外的功能？
解决方法
Whenever we use an aggregate function in sql (MIN,AVG etc),we must always GROUP BY all non-aggregated columns
一般来说这是不正确的.例如MysqL不需要这个,而且sql标准也没有这么说.
It becomes even more intrusive when we use a function or other calculation in our SELECT statement,as this must also be copied to the GROUP BY clause.
一般也不正确. MysqL(也许其他数据库)也允许在GROUP BY子句中使用列别名：
SELECT (2 * (x + y)) / z + 1 AS a,y) AS b,SUM(z) FROM AnotherTable GROUP BY a,b
If this is not the case,then what extra functionality does GROUP BY give us?
指定要分组的唯一方法是使用GROUP BY子句.您不一定会从SELECT中提到的列中推断出它.实际上你甚至不必选择GROUP BY中提到的所有列：
SELECT MAX(col2) FROM foo GROUP BY col1 HAVING COUNT(*) = 2
