#进阶5：分组查询
/*
SELECT
FROM
WHERE
GROUP BY
ORDER BY

查询列表要求是分组函数和 group by 之后出现的字段

1、筛选条件分为两类：
                             数据源         位置          关键字
                 分组前筛选    原始表         group by前    where
                 分组后筛选    分组后的结果集  group by后    having
   
2、分组函数做条件，肯定放在having子句中
3、能用分组前筛选的优先考虑用where筛选
4、支持多个字段分组，逗号隔开没有顺序要求
5、也可以排序
*/

USE myemployees;

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


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


#案例3：查询邮箱中包含a字符的，每个部门的平均工资
SELECT department_id, AVG(salary)
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;


#复杂筛选条件，不能用 where，而应该用 having
#案例5：查询哪个部门的员工个数>2
SELECT count(*), department_id
FROM employees
GROUP BY department_id
HAVING count(*) > 2;


#案例6：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id, Max(salary)
FROM employees
WHERE commission_pct IS NOT NULL   #分组前就能筛选
GROUP BY job_id
HAVING Max(salary) > 12000;


#按表达式分组
#案例7：按员工姓名长度分组，查询每一个组员工个数，筛选员工个数>5的有哪些
SELECT count(*), Length(last_name)
FROM employees
GROUP BY Length(last_name)
HAVING count(*) > 5;

SELECT count(*) AS c, Length(last_name) AS len_name
FROM employees
GROUP BY len_name
HAVING c > 5;


#按多个字段分组
#案例8：查询每个部门每个工种的员工的平均工资
SELECT Avg(salary), department_id, job_id
FROM employees
GROUP BY department_id, job_id;

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;