关于AVG() group by , having, where 字句的使用

[mlovex]

关于使用聚合Avg函数，Having / Where 字句的位置。

请问，为什么 1， 2 没问题； 3可以，4却不行呢？

1. select ID, AVG(Salary) from EMPLOYEE Group by ID Having ID in ('ID1', 'ID2') -- OK
2. select ID, AVG(Salary) from EMPLOYEE where ID in ('ID1', 'ID2') Group by ID -- OK

3. select ID, AVG(Salary) from EMPLOYEE Group by ID Having count(*)>=2 -- OK
4. select ID, AVG(Salary) from EMPLOYEE where count(*)>2 Group by ID -- Not OK

[benluobo]

如果需要在group中需要使用表中的原有字段，可以在group by 前面和后面都可以使用 如果需要在group中需要使用分组后的聚合函数，只能在having 中使用

[mlovex]

找到了，这个是正确解释 COUNT is an aggregate (or column) function rather than a scalar function. Aggregate functions operate over multiple rows. Scalar function operate on a single row. The WHERE clause tests a single row's values and the HAVING clause tests values aggregated or used as grouping values. Thus COUNT is allowed in the HAVING clause, but not in the WHERE clause.