MYSQL-聚合函数

常用的聚合函数

AVG(expr)

• 返回某个数值字段的平均值

SELECT
	department_id,
	AVG(salary)
FROM
	employees
GROUP BY
	department_id;

SELECT AVG(IFNULL(commission_pct,0)),SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)) FROM employees;

SUM(expr)

• 返回某个数值字段的总和

SELECT
	department_id,
	AVG(salary) AS avg,
	SUM(salary) AS sum
FROM
	employees
GROUP BY
	department_id
HAVING
	avg > 10000;

MAX(expr)

• 返回某个数值字段的最大值

MIN(expr)

• 返回某个数值字段的最小值

SELECT
	department_id,
	MAX(salary),
	MIN(salary)
FROM
	employees
GROUP BY
	department_id;

COUNT(expr)

• 返回expr的条数

SELECT
	COUNT(employee_id),
	COUNT(department_id),
	COUNT(commission_pct),
	COUNT(1),
	COUNT(*)
FROM
	employees;

WITH ROLLUP

• 对聚合函数进行求和
• 是对GROUP BY后的第一个字段，进行分组求和。
• 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序

SELECT job_id,AVG(salary) FROM employees GROUP BY job_id WITH ROLLUP;
/*
...
ST_CLERK	2785
ST_MAN	7280
NULL	6461.682243 
*/

HAVING

• 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中
• 使用HAVING的前提是SQL中使用了GROUP BY
• 相比WHERE，HAVING的适用范围更广
• 如果过滤条件中没有聚合函数，WHERE的执行效率更高

#查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
	manager_id,
	MIN(salary)
FROM
	employees
WHERE
	manager_id IS NOT NULL
GROUP BY
	manager_id
HAVING
	MIN(salary) >= 6000;

SQL语法

SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

SQL的执行过程

FROM ...,...-> ON -> (LEFT/RIGNT  JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT