分组函数
- sum 求和
- max 最大值
- min 最小值
- avg 平均值
- count 计数
特点:
1、以上五个分组函数都忽略null值,除了count(*)
2、sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果
4、count的参数可以支持:
字段、*、常量值,一般放1
建议使用 count(*)
分组查询
语法:
SELECT 分组函数,列(要求出现在group by 后面)
FROM 表
WHERE 筛选条件
GROUP BY 分组的列表
ORDER BY 子句
特点:
- 可以按单个字段分组
- 和分组函数一同查询的字段最好是分组后的字段
- 分组筛选
- 针对的表 位置 关键字
- 分组前筛选: 原始表 group by的前面 where
- 分组后筛选: 分组后的结果集 group by的后面 having
- 可以按多个字段分组,字段之间用逗号隔开
- 可以支持排序
- having后可以支持别名
# 引入:查询每个部门的平均工资
# 结果:6461.682243
SELECT AVG(salary) FROM employees;
# 简单的分组查询
# 案例1: 查询每个工种的最高工资
# 结果:8300.00 AC_ACCOUNT (结果集)
SELECT MAX(salary),job_id FROM employees GROUP BY job_id
# 查询每个位置上的部门个数
# 结果:
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id
添加筛选条件
# 案例1: 查询邮箱中包含a字符的,每个部门的平均工资。
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id
# 案例2: 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct is not null GROUP BY manager_id
添加复杂的筛选条件
# 案例1:查询那个部门的员工个数>2
# ①查询每个部门的员工个数
SELECT COUNT(*),department_id FROM employees GROUP BY department_id
# ②根据①的结果筛选,查询哪个部门的员工个数>2
#也就是说执行完group by 的结果后再进行筛选,查询哪个部门的员工个数>2
#这时就需要另外一个关键词,HAVING,放在GROUP BY 以后
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2
# 案例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 MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary) > 5000
GROUP BY 支持表达式和函数,也支持别名,但不通用,比如orcal 不支持GROUP BY 和HAVING 后面放别名
# 案例4:按员工姓名长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5
SELECT COUNT(*) c ,LENGTH(last_name) l FROM employees GROUP BY l HAVING c>5
# 按多个字段分组
# 案例4:查询每个部门每个工种的员工的平均工资。
SELECT AVG( salary),department_id,job_id FROM employees GROUP BY department_id,job_id
# 添加排序
# 案例5:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示。
SELECT AVG( salary) a,department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY a desc