SQL: Aggregate Functions

20th August 2020 at 2:19pm
SQL

聚合函数(aggregate functions)。

不能被使用在 where 从句中

因为 where 是用来判断哪些行被包含在聚合计算中,它必须发生在聚合计算的前面:

-- Wrong: Aggregate functions can not be used in where clause
SELECT city FROM weather WHERE temp_lo = max(temp_lo);

WHERE 与 HAVING 的差别

SELECT city, max(temp_lo)
   FROM weather
   WHERE city LIKE 'S%'
   GROUP BY city
   HAVING max(temp_lo) < 40;
  • WHERE 筛选输入的行,再将这些行用来做聚合运算和分组(group by)
  • HAVING 则是筛选分组后的行;HAVING 语句中应该只用聚合函数作为条件判断,例如上面的 max(temp_lo)