MySql 分组查询

一、分组查询

功能：用作统计使用，又称聚合函数或统计函数或组函数

1.基础用法

1. sum() 求和
2. avg() 求平均值
3. max() 求最大值
4. min() 求最小值
5. count() 计算个数

select sum(字段), avg(字段),max(字段),min(字段),count(字段)from 表名;

2.支持参数类型

1. sum() avg()一般用于处理数值型。max()，min()，count()可以处理任何类型
2. 以上分组函数都忽略null值
3. 可以和distinct搭配使用

select sum(distinct salary),sum(salary) from deployees;

4. count()函数的详细介绍

select count(salary) from employees;	//计算工资列的行数
select count(*) from employees;		//计算表的函数（效率更好）
select count(1) from employees;		//计算1的列数（会自动在表前面增加一列）

5. 和分组函数一同查询的字段要求是 group by后的字段

查询员工表中最大的入职时间和最小入职时间的相差天数（datediff）
select dattediff(max(hiredate),min(hiredate)) as D from employees;

二、分组查询进阶

语法：
select 分组函数，列（要求出现在group by的后面） from 表名 【where 筛选条件】 group by 分组的列表 【order by 字句】
特点:

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

1、简单的分组查询

1. 查询每个工种的最高工资

select max(salary),job_id from employees group by job_id;

2. 查询每个位置上的部门个数

select count(*),loaction_id from departments group by location_id;

2、添加分组前筛选条件查询

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 ;

3、添加分组后的筛选条件

1. 查询哪个部门的员工个数 > 2

select count(*),department_id from employees group by department_id having count(*) > 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 > 102 group by manager_id;
② 查询工资> 5000,的领导编号和最低工资
select min(salary),manager_id from employees where manager_id > 102 group by manager_id having min(salary)>5000;

4、按函数分组

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

① 查询每个长度的员工个数
select count(*),length(last_name) len_name from employees group by length(last_name);
② 添加筛选条件
select count(*),length(last_name) len_name from employees group by length(last_name) having count(*)>5;

5、按多个字段分组

1. 查询每个部门每个工种的员工的平均工资

select avg(salary),department_id,job_id from employees group by department_id,job_id;

6、添加排序

1. 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示

select avg(salary),department_id,job_id from employees group by department_id,job_id order by avg(salary) desc;