分组函数

用作统计使用，又称为聚合函数或同级函数或组函数

SUM(expression)

求和

SELECT SUM(salary) FROM employees;

AVG(expression)

求平均值

SELECT AVG(salary) FROM employees;

MAX(expression)

求最大值

SELECT MAX(salary) FROM employees;

MIN(expression)

求最小值

SELECT MIN(salary) FROM employees;

COUNT(expression)

计算个数

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率：MYISAM 存储引擎下，COUNT(*)的效率高；INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)效率要高一些；
推荐使用COUNT(*)统计行数

SELECT 
    SUM( salary ) 和,
    AVG( salary ) 平均,
    MAX( salary ) 最高,
    MIN( salary ) 最低,
    COUNT( salary ) 个数
FROM
    employees;

平均工资保留两位小数（四舍五入）

SELECT 
    SUM( salary ) 和,
    ROUND(AVG( salary ), 2) 平均,
    MAX( salary ) 最高,
    MIN( salary ) 最低,
    COUNT( salary ) 个数
FROM 
    employees;

支持哪些类型的参数

字符

先试图将字符转换成数值，如果转换成功，则继续运算，否则转换成0，再做运算

SELECT
    SUM(last_name),
    AVG(last_name)
FROM
    employees;
#----------------------------
# result: 0  0

与排序差不多

SELECT 
    MAX( last_name ),
    MIN( last_name )
FROM 
    employees;
# MAX(expr) 和 MIN(expr) 在对英文字符串比大小的时候，先比较首字母的大小（b > a），如果首字母相同，则比较第二个字母的大小，以此类推

日期

SELECT SUM(hiredate), AVG(hiredate) FROM employees;

SELECT MAX(hiredate), MIN(hiredate) FROM employees;

特点

1. SUM、AVG一般用于处理数值型
2. MAX、MIN、COUNT可以处理任何类型
3. 以上分组函数都忽略NULL值
4. 可以和DISTINCT搭配实现去重的运算
5. 一般使用COUNT(*) 用作统计行数
6. 和分组函数一同查询的字段要求是 GROUP BY后的字段

案例

1. 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）

SELECT
    MAX( hiredate ) 最大,
    MIN( hiredate ) 最小,
    (MAX( hiredate ) - MIN( hiredate )) / 1000 / 3600 / 24 AS DIFFRENCE
FROM 
    employees;
    -- 错误写法

使用DATEDIFF(d1, d2)函数，计算日期d1 ~ d2之间相隔的天数

SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) AS DIFFRENCE FROM employees;

2. 查询部门编号为90的员工个数

SELECT
    COUNT(*)
FROM
    employees
WHERE
    department_id = 90;

分组查询

语法

SELECT 分组函数, 分组后的字段
FROM 表
[WHERE 筛选条件]
GROUP BY 分组后的字段
[HAVING 分组后的筛选]
[ORDER BY 排序列表]

特点

1. 和分组函数一同查询的字段必须是GROUP BY后出现的字段
2. 筛选分为两类：分组前筛选和分组后筛选

筛选	针对的表	连接的关键字
分组前筛选	原始表	WHERE
分组后筛选	GROUP BY后的结果集	HAVING

问题

1. 分组函数做筛选能不能放在WHERE后面

   答案：不能，原表中没有分组后的数据

案例

简单的分组

1. 查询每个工种的员工平均工资

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

2. 查询每个位置的部门个数

SELECT COUNT(*), location_id
FROM departments
GROUP BY location_id;

分组前筛选

1. 查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

2. 查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

分组后筛选

1. 查询哪个部门的员工个数>5

   1.1 首先查询每个部门的员工个数

   SELECT COUNT(*), department_id
   FROM employees
   GROUP BY department_id;
   

   1.2 筛选1.1得到的结果

   SELECT COUNT(*), department_id
   FROM employees
   GROUP BY department_id
   HAVING COUNT(*) > 5;
   

2. 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

3. 查询领导编号>102的每个领导手下的员工最低工资大于5000的领导编号和最低工资

SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

添加排序

1. 查询每个工种有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序

SELECT job_id, MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY MAX(salary) ASC;

按多个字段分组

1. 查询每个工种每个部门的最低工资，并按最低工资降序

SELECT job_id, department_id, MIN(salary)
FROM employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC;