进阶5 分组查询

进阶5：分组查询

/*
语法：
select 分组函数（要求出现在group by的后面）
from 表
【where 分组前筛选条件】
【group by 分组的列表】
【having 分组后筛选条件】
【order by 子句】

注意：
（1）明确：where一定放在from后面
（2）order by排序子句一般是放在查询语句的最后面，limit子句除外
（3）分组(group by)前筛选用where（有原字段），分组(group by)后筛选用having（无原字段）
（4）仅where后面：不支持别名
group by后面，having后面，order by后面：支持别名

特点：

1. 分组查询中的筛选条件分为两类
   数据源 位置 关键字
   分组前筛选 原始表 group by子句的前面 where
   分组后筛选 分组后的结果集 group by子句的后面 having

（1）分组函数做条件肯定是放在having子句中：sum，av，max，min，count
（2）能用分组前筛选的，就优先考虑使用分组前筛选
2. group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求）
group by子句支持表达式或函数（用的较少）
3. 也可以添加排序（排序放在整个分组查询的最后）

*/

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

SELECT AVG(salary) FROM employees;

-----------------part1：简单的分组查询-----------------------------------

案例1：查询每个工种的最高工资

解析：【每个“工种”----->按“工种”分组】【常见分组函数：sum，avg，max，min，count】

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

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

解析：【每个“位置”----->按“位置”分组】

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

-----------------part2：添加分组前的筛选【分组前的筛选---->where】-----------------------------------

案例1：查询邮箱中包含a字符的，每个部门的平均工资

解析：【每个“工种”----->按“工种”分组】【%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;

-----------------part3：添加分组后的筛选【分组后的筛选---->having】-----------------------------------

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

解析：step1 查询每个部门的员工个数

step2 根据step1的结果继续筛选，查询哪个部门的员工个数>2

错误输出1：Invalid use of group function --------由于表employees没有count(*)字段，故会出错

SELECT COUNT(), department_id
FROM employees
WHERE COUNT()>2;

正确输出2：【分组后的筛选---->having】

SELECT COUNT(), department_id
FROM employees
GROUP BY department_id
HAVING COUNT()>2;

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

注意：【分组前查询：有奖金】【分组后查询：最高工资>12000】

解析：step1 查询每个工种有奖金的员工的最高工资

step2 根据step1的结果继续筛选，最高工资>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】

解析：step1 查询领导编号>102的每个领导手下的员工的最低工资

step2 根据step1的结果继续筛选，最低工资>5000

SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

-----------------part4：按表达式或函数分组-----------------------------------

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

解析：step1 查询每个长度的员工个数

step2 添加筛选条件

输出1

SELECT COUNT(), LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT() > 5;

输出2【where后面：不支持别名】【group by后面，having后面，order by后面：支持别名】

SELECT COUNT(), LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING COUNT() > 5;

-----------------part5：按多个字段分组-----------------------------------

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

注意：（1）字段先后只影响排列优先级，最后出来的结果（也就是种类数）是一样的

（2）每个部门有多个工种

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

-----------------part6：添加排序【order by一般在最后面，limit子句除外】---------

案例1：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示。要求：部门不为空，平均工资>10000

注意：（1）asc:默认升序 desc：降序

（2）若筛选字段（department_id）同时满足分组前筛选和分组后筛选，优先考虑分组前筛选（where）

输出1

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;

输出2【where后面：不支持别名】【group by后面，having后面，order by后面：支持别名】

SELECT AVG(salary) 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;