SQL语言之DQL语言学习(七)分组查询

语法

select分组函数,列(要求出现在group by的后面)
from 表
where 筛选
group by 分组列表
order by 排序子句

案例一: 查询每个工种的最高工资

SELECT  max(salary),job_id
FROM employees
GROUP BY job_id;

案例二.添加筛选条件,先筛选,后分组

1.查询邮箱中包含a字符的,每个部门的平均工资

SELECT  AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

案例三,先分组 后筛选查询 HAVING
查询每个工种有奖金的员工的最高工资 并且>12000 的工种编号和最高工资

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

案例四:按多个字段分组且排序

查询每个部门, 每个工种的员工的平均工资,并按照平均工资 从高到底排序

SELECT  AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id
ORDER BY AVG(salary ) DESC