语法:
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【having 筛选条件】
【order by 排序的字段】;
PS:分组前和分组后的筛选对比:
对比项目 | 针对的表 | 位置 | 关键字 |
---|---|---|---|
分组前 | 原始表 | group by前 | where |
分组后 | group by之后的结果集 | group by后 | having |
一.简单分组
#案例1:查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#案例2:查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
二.按多个字段进行分组
#案例:查询每个部门每个工种的最低工资,并按部门编号排序
SELECT department_id,job_id,MIN(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY department_id DESC;
三.分组前进行筛选
#案例1:查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2:查询有奖金的每个领导手下员工的平均工资(本题在这里有点超纲)
#step1:找出有奖金的领导
SELECT DISTINCT t1.employee_id
FROM employees AS t1
JOIN employees AS t2 ON t1.`employee_id` = t2.`manager_id`
WHERE t1.`commission_pct` IS NOT NULL;
#step2:找出这些领导手下员工的平均工资
SELECT AVG(salary),manager_id
FROM employees
WHERE manager_id IN (
SELECT DISTINCT t1.employee_id
FROM employees AS t1
JOIN employees AS t2 ON t1.`employee_id` = t2.`manager_id`
WHERE t1.`commission_pct` IS NOT NULL
)
GROUP BY manager_id;
四.分组后筛选
#案例1:查询哪个部门的员工个数>5
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
#案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#即:寻找符合以下条件的工种:在该工种有奖金的员工中,最高工资超过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的编号,以及对应的最低工资
#即找到符合以下条件的领导:1.工号大于102 2.手下的员工中,最低工资>5000.返回该领导的工号以及对应的最低工资
#PS:这里说一下:每条员工信息中的manager_id是指该员工的领导的employee_id
SELECT MIN(salary),`manager_id`
FROM`employees`
WHERE `manager_id`>102
GROUP BY `manager_id`
HAVING MIN(`salary`)>5000;
#案例4:按员工姓名的长度分组,查询每一组的员工个数,并筛选员工个数>5的有哪些
SELECT COUNT(*) AS 人数,LENGTH(`last_name`) AS 姓名长度
FROM `employees`
GROUP BY 姓名长度
HAVING 人数>5;