分组查询 GROUP BY

-- group by 子句-- 要注意！group by 子句一定是放在WHERE的后面/*SELECT 分组函数,列(要求出现在GROUP BY后面)FROM 表[WHERE 筛选条件]GROUP BY 分组列表[ORDER BY 子句]查询列表必须特殊，要求是分组函数和GROUP BY后出现的*/

查询每个部门的平均工资

单使用AVG函数只能查询所有部门一起的平均工资

-- 查询每个部门的平均工资：SELECT AVG(`salary`) 
FROM `employees`; 

引入GROUP BY子句 ，对部门进行一个分组

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

查询每个工种的最高工资

# 查询每个工种的最高工资，SELECT MAX(`salary`),`job_id`FROM `employees`GROUP BY `job_id`;

可以发现，使用GROUP BY子句的前提条件是必须要有聚合函数【组合函数】的查询使用

它们和分组查询形成了一种依赖关系，二者缺一不可

添加分组前，筛选条件

# 前置加入筛选条件-- 查询邮箱中包含a字符的，每个部门的平均工资SELECT AVG(`salary`),`department_id`FROM `employees`WHERE `email` LIKE ‘%a%‘GROUP BY `department_id`;# 查询有奖金的每个人领导手下员工的最高工资 SELECT MAX(`salary`),`manager_id`FROM `employees`WHERE `commission_pct` IS NOT NULLGROUP BY `manager_id`;

添加复杂的筛选条件

【需要对分组后的结果再执行筛选 使用Having】

# 复杂筛选条件# 案例 查询哪些部门的员工个数大于2SELECT COUNT(*) as ‘counts‘,`department_id`FROM `employees`group by `department_id`Having counts > 2; -- 对分组之后再进行筛选的，使用Having子句充当where# 查询每个工种有奖金的员工的最高工资 > 12000 的工种编号，和最高工资SELECT MAX(`salary`) ‘maxSalary‘,`job_id`FROM `employees`where `commission_pct` IS NOT NULLGROUP BY `job_id`having maxSalary > 12000 ;# 查询每个领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资SELECT MIN(`salary`),`manager_id`FROM `employees`WHERE `manager_id` > 102GROUP by `manager_id`having MIN(`salary`) > 5000;

按表达式进行分组

要注意一点：Oracle是不支持GROUP BY & HAVING SQL语句的

-- 按员工姓名的长度分组，查询每一组员工个数，筛选员工个数大于5的-- 先查询每个长度的员工个数SELECT COUNT(‘员工个数‘),LENGTH(`last_name`) AS "名字长度分组"FROM `employees`GROUP BY LENGTH(`last_name`);-- 再筛选大于5的个数SELECT COUNT(‘员工个数‘) AS ‘c‘,LENGTH(`last_name`) AS "名字长度分组"FROM `employees`GROUP BY LENGTH(`last_name`)HAVING c > 5;

按多个字段分组查询

-- 查询每个部门的每个工种的员工的平均工资-- 先确定要查询的要素SELECT AVG(`salary`),`job_id`,`department_id`-- 查询的要素来自于哪张表FROM `employees`-- 对查询的条件进行判断GROUP BY `job_id`,`department_id`;

分组查询是支持排序的

-- 查询每个部门的每个工种的员工的平均工资,按平均工资从高到底显示SELECT AVG(`salary`),`job_id`,`department_id`FROM `employees`GROUP BY `job_id`,`department_id`ORDER BY AVG(`salary`) DESC;-- 增加部门ID不是null的情况SELECT AVG(`salary`),`job_id`,`department_id`FROM `employees`WHERE `department_id` IS NOT NULLGROUP BY `job_id`,`department_id`ORDER BY AVG(`salary`) DESC;-- 平均工资还要高于10000的SELECT AVG(`salary`),`job_id`,`department_id`FROM `employees`WHERE `department_id` IS NOT NULLGROUP BY `job_id`,`department_id`HAVING AVG(`salary`) > 10000 -- 要放在排序的前面，排序留在最后面执行ORDER BY AVG(`salary`) DESC;

GROUP  BY的总结：

-　分组前筛选　

　　数据源是 原始表【就是从数据库查询的表】

　　在GROUP BY 之前 使用【WHERE】子句

-　分组后筛选　

　　数据源是 虚拟表【就是从分组之后返回的结果集】

　　在GROUP BY 之后 使用【HAVING】子句

-　支持多字段分组查询

　　多字段使用逗号隔开

　　字段没有顺序要求

-　支持表达式 & 函数　　

-　支持排序查询

　　排序子句放在SQL的结尾

练习案例：

-- 1、查询个工种ID的员工工资的聚合函数，并按照工种升序SELECT `job_id`,MAX(`salary`),MIN(`salary`),AVG(`salary`),SUM(`salary`)FROM `employees`GROUP BY `job_id`ORDER BY `job_id` ASC;-- 2查询 员工工资最高最低的差距SELECT MAX(`salary`),MIN(`salary`),MAX(`salary`) - MIN(`salary`) ‘相差金额‘FROM `employees`;-- 3查询各个管理者手下员工的最低工资，不得低于6000，没有管理者不计算在内-- 歧义SQL 不正确的筛选>6000SELECT `manager_id`,MIN(`salary`)FROM `employees`WHERE `salary` > 6000 AND `manager_id` IS NOT NULL -- 在分组之前筛选6000GROUP BY `manager_id`;-- 正确SQLSELECT `manager_id`,MIN(`salary`)FROM `employees`WHERE `manager_id` IS NOT NULL -- 在分组之前筛选6000无效GROUP BY `manager_id`HAVING MIN(`salary`) > 6000;-- 4 查询所有部门编号，员工数量，和工资平均值，按工资平均值降序SELECT AVG(`salary`),COUNT(‘员工数量‘),`department_id`FROM `employees`GROUP BY `department_id`ORDER BY AVG(`salary`) ASC;-- 如果没有部门就不算在内的话...SELECT AVG(`salary`),COUNT(‘员工数量‘),`department_id`FROM `employees`WHERE `department_id` IS NOT NULLGROUP BY `department_id` ORDER BY AVG(`salary`) ASC;-- 5 查询每个具有工种ID的员工人数SELECT `job_id`,COUNT(‘人数‘)FROM `employees`GROUP BY `job_id`;