关于分组汇总
1.概述
-
将查询结果按某一列或者多列的值分组。
-
group by
子句 -
分组后聚合函数将作用于每一个组,即每一组都有一个函数值。
-
语法
select 字段列表 from 表名 where 筛选条件 group by 分组的字段; select 字段列表 from 表名 group by 分组的字段 having 筛选条件;
2. having 与 where
-
having
是在分组后对数据进行过滤,即作用于组,选择满足条件的组。 -
where
是在分组前对数据进行过滤,即作用于基本表,选择满足条件的元组。 -
在查询过程中执行顺序:
from > where > group(含聚合)> having > order > select
-
having
后面可以使用聚合函数,where
后面不可以使用聚合函数。
3.其它
-
分组可以按单个字段也可以按多个字段。
-
可以搭配着排序使用。
-
一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率。
基本使用
1.简单分组
-
查询工种编号和该工种的员工人数 num
select job_id, count(*) as num from employees group by job_id;
-
查询工种编号和该工种的平均工资 avg_salary(去掉小数部分)
select job_id, truncate(avg(salary), 0) as avg_salary from employees group by job_id;
2.分组前筛选(where)
-
查询部门编号和该部分员工邮箱中包含 a 字符的最高工资
select department_id, max(salary) from employees where email like '%a%' group by department_id;
-
查询管理者编号,以及该领导手下有提成的员工的平均工资(去掉小数)avg_salary
select manager_id, truncate(avg(salary), 0) as avg_salary from employees where commission_pct is not null group by manager_id;
3.分组后筛选(having)
-
查询部门编号和该部门的员工人数 num,只要 num>5 的数据
select department_id, count(*) as num from employees group by department_id having num > 5;
-
查询管理者编号和该领导手下员工的最低工资 min_salary,只要 min_salary>5000 的数据
select manager_id, min(salary) as min_salary from employees group by manager_id having min_salary > 5000;
-
查询管理者编号和该领导手下员工的最低工资 min_salary,只要 min_salary >= 6000 的数据,没有管理者的员工不计算在内
select manager_id, min(salary) as min_salary from employees where manager_id is not null group by manager_id having min_salary >= 6000;
4.添加排序
-
查询工种编号,以及该工种下员工工资的最大值,最小值,平均值,总和,并按工种编号降序排序
select job_id, max(salary), min(salary), avg(salary), sum(salary) from employees group by job_id order by job_id desc;
-
查询工种编号和该工种有提成的员工的最高工资 max_salary,只要 max_salary>6000 的数据,对结果按 max_salary 升序排序
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;
5.按多个字段分组
-
查询部门编号、工种编号和该部门和工种的员工的最低工资 min_salary,并按最低工资降序排序
select department_id, job_id, min(salary) as min_salary from employees group by department_id, job_id order by min_salary desc;
一代人终将老去,但总有人正年轻。 —— 刺猬乐队《火车驶向云外,梦安魂于九霄》