1、语法格式
语法:select group_funtion(column), column(必须在分组函数后)
from table
[where condition]
[group by group_by_expression]
[order by column];
注意:查询列表必须特殊,要求是分组函数和group by后出现的字段,where在group前面。
示例:查询每个工种的最高工资
select max(salary), job_id from employees group by job_id;
2、添加分组前筛选 使用where,在group之前
3、添加分组后筛选 使用having,在group之后,分组函数做条件,肯定是放在having子句中,分组函数肯定是分组后筛选。
案例:查询哪个部门的员工个数大于2
第一步:查询每个部门的员工数:
select count(*), department_id from employees group by departmeng_id;
第二步:根据第一步的机构进行筛选,查询哪个部门的员工数大于2:
select count(*), department_id from employees group by departmeng_id having count(*)> 2;
4、group by可以按照表达式分组,也可以按照多个字段分组。
案例1: 按照字符串长度分组,帅选员工个数大于5
select count(*), length(last_name) name_len from employees group by length(last_name) having count(*)>5;
也可以使用别名:
select count(*) c, length(last_name) name_len from employees group by name_len having c>5;
案例2:查询每个部门每个工种的员工数
select count(*), department_id, job_id from employees group by department_id, job_id;