分组查询
把一张表拆分成多个小组,进行查询得到我们想要的结果
GROUP BY子语句法
可以使用GROUP BY子局将表中的数据分成若干组
SELECT 分组函数,列 (还要求出现在group by的后面)
FROM 表
【where 条件筛选】
group by 分组的列表
【order by 子句】
注意:
查询列表必须特殊,要求是分组函数和group by后出现的字段
简单分组查询练习
1.查询每个部门的平均工资
SELECT AVG(salary) FROM employees;
2.查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
3.查询每个位置上的部门个数
SELECT CONT(*),localtion_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 CONT(*),department_id FROM employees GROUP BY department_id;
- 根据上一步结果进行筛选,查询哪个部门的员工个数大于2
SELECT CONT(*),department_id FROM employees GROUP BY department_id HAVING CONT(*)>2;
2.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
- 每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id;
- 根据上一步结果筛选最高工资大于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的领导编号是哪个,以及最低工资
- 查询领导编号>102的每个领导手下的最低工资
SELECT MIN(salary),manager_id FROM employees WHERE manager_id >120 GROUP BY manager_id;
- 最低工资>5000的领导编号是哪个
SELECT MIN(salary),manager_id FROM employees WHERE manager_id >120 GROUP BY manager_id HAVING MIN(salary)>5000 ;
按表达式或函数分组
1.按员工姓名的长度分组,查询每一组的员工,筛选员工个数>5的有哪些
- 查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) len_name FROM employees GROUP BY LENGTTH(last_name);
- 筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) len_name FROM employees GROUP BY LENGTTH(last_name) HAVING CONT(*)>5;
按多个字段分组
1.查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY deparment_id,job_id;
添加排序
SELECT AVG(salary),department_id,job_id FROM employees WHERE deparment_id IS NOT_NULL GROUP BY job_id,department_id ORDER BY AVG(salary) DESC;
练习
1.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>=6000;
2.查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a FROM employees GROUP BY department_id ORDER BY a DESC;
继续阅读

我的微信
这是我的微信扫一扫
评论