3.1：分组查询支持字段和函数分组
# 分组查询
/*
分组数据 group by，该语句可以iang表中数据分成若干组
用法如下：
SELECT COLUMN,GROUP BY(COLUMN)
FROM TABLE
[WHERE CONDITION]
[GROUP BY GROUP BY expression]
[ORDER BY column]
特点：1：按照筛选条件可以分为
分组前刷选：原始表 在group_by前面用where
分组后刷选：分组后的结果集 在group_by后面用having
分组后筛选都是用having，能在分组前筛选的尽量在分组前筛选
特点：2：可以支持多个字段分组查询，
特点：3：支持升降序
*/
#引入，查询每个部门的平均工资。
#1：每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#2，每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
# 添加筛选条件
#3，查询邮箱包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email like '%a%'
GROUP BY department_id;
#4，有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct is not NULL
GROUP BY manager_id;
#添加复杂的筛选条件
#1：查询哪个部门的员工个数大于二？分组后的查询
#WHERE限制的条件是来自table的，
#而这种限制条件来之分组查找后的，应该用having。
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
HAVING MAX(salary)>12000;
#3 查询领导编号大于102的每个领导手下员工最低工资大于5000的最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#按表达式或者函数分组
#案例：按照员工姓名长度分组，查询每一种员工个数，筛选员工个数>5的有哪些？
#1：
SELECT count(*) c,LENGTH(last_name) len
FROM employees
GROUP BY len
HAVING c>5;
#按多个字段分组,加入多个字段即可。
#案例1：查询每个部门，每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
#案例2：查询每个部门，每个工种的员工的平均工资，并且按照平均工资的高低进行显示
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id is not null && department_id>90
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(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 ASC;
#2：查询员工最高工资和最低工资的差距
SELECT MAX(salary),MIN(salary),MAX(salary)- MIN(salary)
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 COUNT(*),AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
#5 ：选择具有各个job_id的员工人数
SELECT COUNT(*),job_id
from employees
where job_id is not NULL
GROUP BY job_id
ORDER BY COUNT(*);
