1.分组查询的简单案例
1 # 1.分组查询的语法规则: 2 select分组函数,列(该列要求是出现在groupby后面的列) 3 from表 4 [where筛选条件] 5 groupby分组的列表 6 [order by子句] 7 注意:查询列表要求是分组函数和groupby后面出现的字段
8 # 2.查询每个工种的最高工资 9 # 分析:最高工资应该使用max(salary)统计函数, '每个工种' 要求按工种进行分组 10 select max(salary), job_id from employees groupby job_id;
11 # 3.查询每个位置(location_id)上的部门个数 12 # 分析: '每个位置' 要求按位置,即按location_id进行分组查询 13 select count(*), location_id from departments groupby location_id;
2.添加简单筛选条件的分组查询
1 # 1.查询邮箱中包含a字符的, 每个部门的平均工资 2 select avg(salsry), department_id 3 from employees 4 where email like "%a%" 5 groupby department_id;
6 # 2.查询有奖金的每个领导手下员工的最高工资 7 select max(salary), manager_id 8 from employees 9 where commission_pct isnotnull 10 groupby manager_id;
3.添加复杂筛选条件的分组查询(分组后筛选)
1 # 1.分组查询添加筛选条件: 2 1).分组前筛选 3 2).分组后筛选
4 # 2.分组前筛选: 分组前筛选是分组查询是以原始表为基础对查询进行条件限制, 使用where关键字, where关键字紧跟在from子句后面. 5 # 3.分组后筛选: 分组后筛选是分组查询后的结果作为再次对数据进行筛选的条件, 二次筛选使用having关键字, 位于group by子句后面. 6 如:筛选出每个部门的员工最高工资后,再筛选出最高工资大于10000的员工 7 select max(salary), department_id 8 from employees 9 groupby department_id 10 having max(salary)>10000;
11 # 4.分组前筛选与分组后筛选对比 12 数据源 位置 关键字 13 分组前筛选 原始表 groupby子句前 where 14 分组后筛选 分组后的结果集 groupby子句后 having
15 # 5.注意: 16 1).分组函数做条件可定放在having子句中 17 2).考虑到性能,能使用分组前筛选的,就优选考虑使用分组前筛选 18 3).groupby子句支持单字段分组,也支持多字段分组,多个字段用盗号分开,不区分分组字段的顺序. 19 4).分组查询中可以添加排序查询(排序放在整个分组查询的最后)
1 # 1.查询哪个部门的员工个数>2 2 select count(*), department_id 3 from employees 4 groupby department_id 5 having count(*)>2;
6 # 2.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 7 select max(salary), job_id 8 from employees 9 where commission_pct isnotnull 10 groupby job_id 11 having max(salary)>12000;
12 # 3.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个, 以及其最低工资 13 select min(salary), manager_id 14 from employees 15 where manager_id>102 16 groupby manager_id 17 having min(salary)>5000;
4.按表达式或函数分组(不常用)
1 # 按员工姓名的长度分组, 查询每一组的员工个数, 筛选员工个数>5的有哪些 2 select count(*), length(last_name) 3 from employees 4 groupby length(last_name) 5 having count(*)>5;
5.按多个字段分组
1 # 查询每个部门每个工种的员工的平均工资 2 select avg(salary), department_id, job_id 3 from employees 4 groupby department_id, job_id;
6.添加排序的分组查询
1 # 查询每个部门每个工种的员工的平均工资, 并且按平均工资的高低显示 2 select avg(salary), department_id, job_id 3 from employees 4 groupby deparment_id, job_id 5 order by avg(salary) desc;
7.小练习
1 # 1.查询各job_id的员工工资的最大值,最小值,平均值,总和,并按job_id升序 2 select max(salary), min(salary), avg(salary), sum(salary), job_id 3 from employees 4 groupby job_id 5 order by job_id;
6 # 2.查询员工最高工资和最低工资的差距并将差距命名为字段DIFFERENCE 7 select max(salary)- min(salary)as DIFFERENCE 8 from employees;
9 # 3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内 10 select min(salary), manager_id 11 from employees 12 where manager_id isnotnull 13 groupby manager_id 14 having min(salary)>=6000
15 # 4.查询各个部门的编号,员工数量和工资平均值,并按平均工资降序 16 select department_id, count(*), avg(salary) 17 from employees 18 groupby departent_id 19 order by avg(salary) desc;
20 # 5.选择具有各个job_id的员工人数 21 select conunt(*), job_id 22 from employees 23 groupby job_id