视频学习链接https://www.bilibili.com/video/av59623481/?p=63
语法:
select 分组函数(计算字段), 分组字段
from 表
[where 条件筛选]
group by 分组的字段
[order by 子句]
特点:
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
针对的表 位置 关键字
分组前筛选: 原始表 group by的前面 where
分组后筛选: 分组后的结果集 group by的后面 having
4、可以按多个字段分组,字段之间用逗号隔开
5、可以支持排序
6、group by和having可以支持别名
例:
1. 查询每个工种的最高工资:SELECT MAX(salary), job_id FROM employees GROUP BY job_id;
2. (分组后的筛选)查询哪个部门的员工个数大于2:
1. SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
3. (分组前筛选)查询每个工种有奖金的员工的工种的最高工资和工种编号:
1. SELECT MAX(salary), job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id;
4. (分组前于分组后结合筛选)查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资:
1. SELECT MAX(salary), job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000;
5. (按多个字段分组)查询每个部门每个工种的员工的平均工资:
1. SELECT AVG(salary), department_id, job_id FROM employees GROUP BY department_id, job_id;
6. (分组后排序)查询每个部门每个工种的员工的平均工资,并且按平均工资高低排序:
1. SELECT AVG(salary), department_id, job_id FROM employees GROUP BY department_id, job_id ORDER BY AVG(salary) DESC;