SQL-分组查询

分组查询

语法：

select 分组函数,列（要求出现在group by的后面）
from 表 
where 条件 
group by 分组的列表
order by

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：

分组查询中的筛选条件分为两类：
分组前筛选（分组源：原始表；位置：group by 前面；关键字：where）、
分组后筛选（分组源：分组后的结果集；位置：group by 后面；关键字：having）
注意：I：分组函数做条件肯定是放在having子句中
II：能用分组前筛选的，就优先考虑使用分组前筛选
group by字句支持单个字段分组、多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式、函数
也可以添加排序，排序在分组查询之后

引入：查询每个部门对平均工资：

image.png

分组前的查询

案例1:查询每个工种的最高工资

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

案例2:查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM departments -- 部门表
GROUP BY location_id;

添加筛选条件

案例1:查询邮箱中包含a字符的，每个部门的平均工资

-- 注意：where放在from之后，group by之前
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

-- 步骤1.查询每个部门的员工个数
-- 步骤2.根据步骤1的结果进行筛选，查询>2
SELECT COUNT( * ),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

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

-- 步骤一：查询每个工种有奖金的员工的最高工资
-- 步骤二：根据步骤一的结果，继续筛选：最高工资>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的领导编号是哪个，以及其最低工资

-- 步骤1:查询领导编号>102每个领导手下员工最低工资
-- 步骤2:最低工资>5000
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

按表达式或函数分组

案例：按照员工名字的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

-- 查询每个长度的员工个数
-- 添加筛选条件
SELECT COUNT( * )
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

按多个字段分组

案例：查询每个部门每个工种对员工对平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id; -- 顺序可以颠倒

添加排序

案例：查询每个部门每个工种对员工对平均工资，并且按照平均工资的高低显示出来

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

练习

案例1 查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id DESC;

案例2 查询员工最高工资和最低工资的差距

SELECT MAX(salary)-MIN(salary)
FROM employees;