DQL---分组查询

DQL—分组查询

5. 分组查询

/*
引入：查询每个部门的平均工资
语法：select 分组函数， 列 from 表名 where 条件 group by 分组列表 order by 子句
特点：
    1. 分组中的筛选条件分为两类
                       数据源        位置              关键字
       分组前筛选      原始表      group by 子句前面    where
       分作后筛选  分组后的结果集   group by 子句后面   having
       
       分组函数作为条件一定放于having子句中
       优先考虑分组前筛选
    2. 支持单个字段/多个字段/表达式/函数分组作为分组列表
    3. 也可以在语句最后添加排序语句
*/
## 简单分组查询
SELECT * FROM employees;
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
## 添加分组前的筛选条件
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

## 添加分组后的筛选条件
 # 查询那个部门员工个数>2
 SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
 # 查询每个工种有奖金的员工的最高工资大于12000的公众编号和最高工资
 SELECT job_id,MAX(salary)
 FROM employees
 WHERE commission_pct IS NOT NULL
 GROUP BY job_id
 HAVING MAX(salary)>12000;
 # 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个以及最低工资
 SELECT 
 manager_id,MIN(salary)
 FROM
 employees 
 WHERE manager_id > 102
 GROUP BY manager_id
 HAVING MIN(salary)>5000
 ORDER BY MIN(salary);

##按表达式分组筛选
SELECT COUNT(*) AS 个数,LENGTH(last_name) AS 姓名长度
FROM employees
GROUP BY LENGTH(last_name)
HAVING  COUNT(*)>5;

## 按多个字段分组
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;