mysql 分组查询

2022-08-15 22:37:44

分组函数

分组函数

分组数据： group by,可以使用group by子句将表中的数据分为若干组
 /*
 select  column,group_function(column)
 from    table
 [where  condition]
 [group by  group_by_expression]
 [order by column];
 */
 #注意:查询列表必须特殊，要求是分组函数和group by后出现的字段
 # 明确：where 一定要放在from后面

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

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

SELECT AVG(salary) FROM employees;

案例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字符的，每个部门的平均工资

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`;

添加分组后的筛选条件：having

案例1：查询哪个部门的员工个数>2
分部讨论
1.查询每个部门的员工个数
2.根据1的结果进行查询，查询哪个部门的个数>2

select count(*),`department_id`
from employees
group by department_id
having count(*)>2;

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

查询每个工种有奖金的员工的最高工资
根据1的结果筛选，最高工资>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
添加筛选条件，最低工资>5000

select min(salary),`manager_id`
from employees
where `manager_id`>102
group by `manager_id`
having MIN(salary)>5000;

按表达式或函数进行分组

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

查询每个长度的员工个数
添加筛选条件

select count(*),length(last_name) as 姓名长度
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`;

添加排序

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示,部门编号不为null，平均工资大于10000

SELECT AVG(salary),`department_id`,`job_id`
FROM `employees`
where `department_id` is not null
GROUP BY `department_id`,`job_id`
having AVG(salary) >10000;
order by AVG(salary) desc;

作业

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

select max(salary),avg(salary),sum(salary),job_id
from employees
group by job_id
order by job_id asc;

查询员工最高工资和最低工资的差距（difference）

select max(salary)-min(salary) as difference
from employees;

查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不在计算内

select min(salary),`manager_id`
from employees
where manager_id is not null
group by manager_id
having min(salary) > 6000;

查询所有部门的编号，员工数量和工资的平均值，并按平均工资降序

select count(*),avg(salary),`department_id`
from employees
group by department_id
order by avg(salary) desc;

选择具有各个job_id的员工人数

select count(*),job_id
from employees
group by job_id;

码农公寓