分组查询
- SELECT 查询列表 FROM 表名 WHERE 筛选条件 GROUP BY 分组字段 HAVING 字段
- 查询列表往往是 分组函数和被分组的字段
- 执行顺序:from→where→group by→having→select→order by
筛选的基表 | 使用的关键词 | 位置 | |
分组前的筛选 | 原始表 | where | group by的前面 |
分组后的筛选 | 分组后的结果集 | having | group by的后面 |
#1、简单分组
#案例1:查询每个工种的员工的平均工资
SELECT job_id,SUM(salary) FROM employees GROUP BY job_id;
#案例1:查询每个领导的手下人数
SELECT manager_id,COUNT(*) FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;
#2、实现分组前的筛选——WHERE
#案例1:查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary) 最高工资 ,department_id FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2:查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary) 平均工资,manager_id FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#3、实现分组后的筛选——HAVING
#案例1:查询哪个部门的员工个数大于5
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
#案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
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;
#4、可以实现排序
#案例:查询有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary);
#5、按多个字段进行分组
#案例:查询每个工种、每个部门的最低工资,并按最低工资降序排序
#提示:工种和部门都一样才是一组
SELECT job_id,department_id,MIN(salary) 最低工资
FROM employees
GROUP BY job_id,department_id
ORDER BY MIN(salary) DESC;