#进阶5：分组查询
/*
语法：
    select 分组函数 ，列（要求出现在group by后面的列）
    from 表
    【where 筛选条件】
    group by 分组的列表
    【order by 子句】
注意：
    查询列表必须特殊，要求是分组函数和group by 后出现的字段（列）


特点:
    1.先筛选再分组；先where 再 group by
    2.分组后筛选：    先group by 再 having
    注意：分组函数做筛选条件肯定放在  having 子句中！且能用先筛选就先筛选再分组。
    3.支持单字段、多字段（无顺序）；也支持函数和表达式
    4.也可以添加排序，order by 最后~
    
小知识点    
    1.group by和having支持别名~但where不支持(仅mysql支持)
*/

#引入：查询每个部门的平均工资
SELECT AVG(salary) FROM employees; #不能解决
SELECT AVG(salary) ,department_id #引入 group by 
FROM employees
GROUP BY department_id; 

#按字段分组

#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;


#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#添加简单筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资

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

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

SELECT last_name,MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id
ORDER BY MAX(salary);
#验证一下last_name是否正确
SELECT salary,manager_id,last_name
FROM employees
WHERE last_name='Tucker';

#添加复杂筛选条件（分组后的筛选---先分组后筛选having）

#案例1：查询员工个数大于2的部门
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id #先确定各部门的员工数
HAVING COUNT(*)>2;    #再选择大于2的部门id

#案例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 #筛选领导编号大于102
GROUP BY manager_id #根据各领导分组
HAVING MIN(salary)>5000; #最低工资大于5000


#按表达式或函数分组；即group by 后支持表达式或函数;group by和having支持别名~但where不支持

#案例1：按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#别名
SELECT COUNT(*) c ,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING c>5;

#按多字段分组；与顺序无关

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


#t添加排序

#案例1：查询每个部门下每个工种的员工的平均工资>5000的工资，并降序
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
HAVING AVG(salary)>5000
ORDER BY AVG(salary) DESC;

#==============================Test=========================

#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.查询员工最高工资和最低工资的差距

SELECT MAX(salary)-MIN(salary) DIFFERENCE
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
GROUP BY job_id;