SQL语句（三）分组函数和分组查询
目录
一、分组函数
功能：统计信息
分类：
sum求和avg平均值max最大值min最小值count计算个数
特点
- 参数支持类型
| 函数 | 参数支持类型 |
|---|---|
sum avg |
具有可加性的数据 |
max min |
具有可比较性的数据 |
count |
计数`非空的数据个数，都支持 |
- 都忽略
null值 - 可以搭配
distinct使用
1. 各函数的简单使用
获取平均工资和工资总和：
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT SUM(salary) 总工资, ROUND(AVG(salary),2) 平均工资, MIN(salary) 最低工资,
MAX(salary) 最高工资, COUNT(salary) 总数
FROM employees;
2. 搭配distinct的使用
SELECT COUNT(DISTINCT job_id) AS 工种数量, COUNT(job_id)
FROM employees;
3. COUNT 统计行数
SELECT COUNT(*) FROM employees;
加入常量值1，相当于加入一列的常量值后统计个数
SELECT COUNT(1) FROM employees;
4. 和分组函数一同查询的字段要求是group by后的字段
SELECT AVG(salary),salary
FROM employees;#(×)
二、分组查询
SELECT xxx字段，分组函数 <--
FROM 表
[WHERE condition]
GROUP BY xxx字段 <-- 按xxx字段分组（相同值为一组）
[ORDER BY xx]
分组查询中，SELECT的字段需要和GROUP BY后的字段对应
实际执行流程：
- 按照
xxx字段（或函数等）进行分组 - 对每个分组进行按分组函数进行特定的统计
- 返回每个组的统计结果
1. 简单应用
查询每个工种的最高工资
SELECT job_id, MAX(salary)
FROM employees
GROUP BY job_id;
统计每个位置（城市）的部门个数
SELECT location_id, COUNT(1)
FROM departments
GROUP BY location_id;
2. 添加筛选条件
| 数据源 | 位置 | |
|---|---|---|
| 分组前筛选 | 原始表 | GROUP BY前 |
| 分组后筛选 | 分组后的结果集 | GROUP BY后 |
① 分组前的筛选
统计各个上级的下属有奖金的员工的最高工资
SELECT manager_id, MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
② 分组后的筛选
使用HAVING语句：
SELECT xxx字段，分组函数
FROM 表
[WHERE condition]
GROUP BY xxx字段
[ORDER BY xx]
HAVING xxx条件 <--在最后添加
查询哪个部门的员工个数>2
- 查询每个部门的员工个数
SELECT department_id, COUNT(1) AS cnt
FROM employees
GROUP BY department_id;
- 由
1的结果进行筛选（员工个数>2）
SELECT department_id, COUNT(1) AS cnt
FROM employees
GROUP BY department_id
HAVING cnt>2;
查询领导编号>102的，每个其手下员工的最低工资>5000，满足条件的领导的对应编号
SELECT manager_id, MIN(salary) AS 手下员工最低工资
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000
3. 按表达式或函数分组
按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5的有哪些
SELECT LENGTH(last_name) as NameLength, COUNT(1) as cnt
FROM employees
GROUP BY NameLength
HAVING cnt > 5
ORDER BY NameLength
4. 按多个字段分组
查询每个（部门，工种）的员工的平均工资
SELECT CONCAT( "(", department_id, ", ", job_id,")" ), AVG(salary)
FROM employees
GROUP BY department_id, job_id;
添加排序和筛选：
SELECT CONCAT( "(", department_id, ", ", job_id,")" ), AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id, job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;
