mysql数据库分组查询总结

语法：
select 分组函数，列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
针对的表 位置 连接的关键字
分组前筛选 原始表 group by前 where

分组后筛选 group by后的结果集 group by后 having

问题1：分组函数做筛选能不能放在where后面
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

分组查询的介绍

说明：图中所标注的①必须出现在 group by 后面，否则没有意义

分组查询—简单使用

小技巧：一般情况下每个什么就是对它分组

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

分组查询—添加分组前筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;


#案例2：查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

分组查询—添加分组后筛选(having)

如果删选的字段在原始表中不存在，则使用having关键进行筛选，否则使用where关键字进行筛选

案例1：查询哪个部门的员工个数>5

------------使用子查询-------
select t.department_id,t.c from(
select department_id,count(*) c
from employees
group by
department_id
) t where t.c>5;
---------使用having------------
select department_id,count(*) c
from employees
group by
department_id
having c>5

如果实在写不下来sql就可以用分步写

#第①步查询每个部门的员工个数
select department_id,count(*)
from employees
group by department_id;
#第②步根据第①步的结果进行筛选，查询哪个部门的员工个数>2,count(*)在原始表中不存在，所以只能用having
select department_id,count(*)
from employees
group by department_id;
having count(*)>2

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

#①查询每个工种有奖金的员工的最高工资,奖金是原始表中的字段，所以筛选可以放在from后面用where关键字筛选
select job_id,max(salary)
from employees
where commission_pct is not null
GROUP BY job_id
#②根据①结果继续筛选，最高工资>12000
select job_id,max(salary)
from employees
where commission_pct is not null
GROUP BY job_id
having max(salary)>12000

案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

#①查询每个领导手下的员工固定最低工资
select min(salary),manager_id
from employees
group by manager_id
#②添加筛选条件编号大于102，编号在原始表中，所以可以放在from后面用where关键字进行筛选
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 #最低工资 这个字段在原始表中没有，所以用having关键字进行筛选

分组查询—添加筛选的总结

①分组函数做条件可定是放在having字句中,因为分组函数在原始表中可定是没有的

②为了考虑性能问题，能用分组前筛选的尽力用分组前筛选。

分组查询—按函数分组

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

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

mysql支持别名，oracle可能不支持，注意别名不要和原名表中的字段一样

select count(*) c,length(last_name) na
from employees 
group by na
having c>5;

分组查询—按多个字段分组

案例：查询每个工种每个部门的最低工资

select department_id,job_id,min(salary)
from employees
group by job_id,department_id

分组查询—添加排序

案例：查询每个工种每个部门的最低工资,并按最低工资降序

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

【案例讲解】分组查询

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

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

2.查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM employees;

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

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

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

SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;

5.查询具体各个job_id的员工人数

SELECT COUNT(*) 个数,job_id
FROM employees
GROUP BY job_id;