进阶5：分组函数

#进阶5：分组函数
        /*

        语法：
            select 分组函数，列（要求出现在group by的后面）
            from 表
            【where 筛选条件】
            group by 分组列表
            【order by 子句】
        注意：
            查询列必须特殊，要求是group by后面出现的字段
        特点：
            1、分组查询中的筛选条件分为
                    数据源         位置          关键字
            分组前筛选   原始表         group by子句的前面   where
            分组后筛选   分组后的结果集     group by子句的后面   having
            
                1、分组函数做条件肯定是放在 having子句中
                2、能用分组前筛选的，就优先考虑分组前筛选
            2、group by子句支持单个字段分组，多个字段分组（多个字段用逗号分开，没有顺序要求），表达式或函数（用得较少）
            3、也可以添加排序（排序放在整个分组查询的最后）
            
            
        */

        #引入：查询每个部门的平均工资
        SELECT `department_id`, AVG(salary) FROM employees GROUP BY `department_id`;

        #案例1： 查询每个工种的最高工资
        SELECT `job_id`, MAX(salary) 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 MAX(salary), `manager_id` 
        FROM `employees` 
        WHERE `commission_pct` IS NOT NULL
        GROUP BY `manager_id`;

        #添加复杂的筛选条件
        #添加分组后的筛选条件
        #案例1：查询那个部门的员工个数>2

        SELECT  d.`department_id` FROM
        (SELECT `department_id`, COUNT(*) AS c FROM `employees` GROUP BY `department_id`) AS d
        WHERE d.c > 2;

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

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

        SELECT `job_id`, MAX(`salary`) FROM `employees` WHERE `commission_pct` IS NOT NULL AND `salary` > 12000 
        GROUP BY `job_id`;

        SELECT `job_id`, MAX(`salary`) FROM `employees` WHERE `commission_pct` IS NOT NULL 
        GROUP BY `job_id` HAVING MAX(`salary`) > 12000;

        #案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导的编号是哪个，以及其最低工资

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


        #按表达式或函数分组

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

        #按多个字段分组

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

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


        #添加排序
        #案例：查询每个部门每个工种员工的平均工资高于10000的，并且按平均工资的高低显示

        SELECT `department_id`, `job_id`, AVG(`salary`) 
        FROM `employees` 
        WHERE `department_id` IS NOT NULL
        GROUP BY `department_id`,`job_id` 
        HAVING AVG(`salary`) >10000
        ORDER BY AVG(`salary`) DESC;