本篇文章主要是对MySQL学习时的一些总结,作为学习笔记记录。
数据来源
数据部分来自于b站尚硅谷MySQL课程
分组查询
语法
SELECT querylist FROM tablename WHERE conditions GROUP BY groupfield ORDER BY sortcondition;
特点
- 和分组函数一同查询的字段为group by后出现的字段
- 筛选分为两类,分组前筛选和分组后筛选,一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率
操作对象 | 位置 | 关键字 | |
分组前筛选 | 原始表 | group by前 | where |
分组后筛选 | group by后的结果 | group by后 | having |
- 分组可以按单个字段也可以按多个字段
主要操作
1. 简单分组查询
#案例1:查询每个工种的员工平均工资
SELECT
AVG(salary),
job_id
FROM
employees
GROUP BY job_id ;
#案例2:查询每个位置的部门个数
SELECT
COUNT(*),
location_id
FROM
departments
GROUP BY location_id ;
2. 分组前筛选
#案例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 ;
3. 分组后筛选
#案例1:查询哪个部门的员工个数>5
SELECT
COUNT(*) AS num,
department_id
FROM
employees
GROUP BY department_id
HAVING num > 5 ;
#案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
job_id,
MAX(salary) AS max_salary
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING max_salary > 12000 ;
#案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT
manager_id,
MIN(salary) AS min_salary
FROM
employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING min_salary > 5000 ;
4. 添加排序
#案例:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT
job_id,
MAX(salary) AS max_salary
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING max_salary > 6000
ORDER BY max_salary ASC ;
5. 按多个字段分组
#案例:查询每个工种每个部门的最低工资,并按最低工资降序
SELECT
MIN(salary) AS min_salary,
job_id,
department_id
FROM
employees
GROUP BY job_id,
department_id
ORDER BY min_salary DESC ;