DQL 语言 --分组查询 08

一、语法

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

 

二、特点

　　1、和分组函数一同查询的字段必须是group by后出现的字段
　　2、筛选分为两类：分组前筛选和分组后筛选

 

 　　　　　　　　　　　　 针对的表  　　 　　　　　　位置  　　　　　　　　连接的关键字

　　　　分组前筛选               原始表                             group by前                             where
 
　　　　分组后筛选              group by后的结果集         group by后                             having

 

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

 

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

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

 

　　3、分组可以按单个字段也可以按多个字段（用逗号隔开）

　　4、可以搭配着排序使用  （排序放在整个分组查询的最后）

 

#1.简单的分组

#案例1：查询每个工种的员工平均工资

SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

 

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

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

 #2、可以实现分组前的筛选

#案例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;

 

#3、分组后筛选

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

　　分解：

　　　　#①查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

　　　　#② 筛选刚才①结果

SELECT COUNT(*),department_id
FROM employees

# where Count(*) > 5   错误写法，where后不能跟分组函数

GROUP BY department_id

HAVING COUNT(*)>5;      #应该用having

 

#案例2：每个工种有奖金的员工的最高工资>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 manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

 

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

#①查询每个长度的员工个数

SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);

#②添加筛选条件  

SELECT COUNT(*) c,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name      #别名
HAVING c>5;

 

 #4.按多个字段分组

#案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY job_id,department_id;

 

#5.添加排序

#案例：查询每个部门每个工种的员工平均工资，并且按平均工资>10000的高低显示

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY job_id,department_id
HAVING AVG(salary)>10000

ORDER BY AVG(salary) DESC;

 

 

 

测试：查询员工最高工资和最低工资的差距（DIFFERENCE）

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