一,语法
SELECT 分组函数,列(要求出现在group by的后面)
FROM 表
【WHERE 筛选条件】
group by 分组的列表
【order by的句子】
二,注意
查询列表比较特殊,要求是分组函数和group by后出现的字段。
特点:
1.分组查询中筛选条件分为两类:
分组前筛选 | 分组后筛选 | |
数据源 | 原始表 | 分组后的结果集 |
位置 | GROUP BY子句的前面 | GROUP BY子句的后面 |
关键字 | WHERE | HAVING |
是否支持别名 | 否 | 是 |
2.分组函数做条件,一定放在HAVING子句中。
3.能用分组前筛选就用分组前筛选。
4.GROUP BY子句支持单个字段分组,多个字段分组(多个字段用逗号隔开,无顺序要求)。
5.也可以添加排序,放在整个分组查询的最后。
三,案例
案例1:查询每个工种的平均工资
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;
案例2:查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
添加分组前的查询条件
案例3:查询包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%'
GROUP BY department_id;
案例4:查询有奖金的每个领导手下员工的工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
添加分组后的查询条件
案例5:查询哪个部门员工数>2
/* (1).查询每个部门员工个数
(2).根据(1)的结果进行筛选,查询哪个部门员工数>2 */
SELECT COUNT(*),department_id FROM employees GROUP BY department_id
HAVING COUNT(*)>2;
/* HAVING用于分组后的筛选 */
案例6:查询每个工种有奖金的员工的最高工资
/* (1).每个工种有奖金员工的最高工资
(2).查询最高工资>12000的 */
SELECT MAX(salary),job_id
FROM emploryees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
案例7:查询领导编号>102的每个领导手下的员工固定最低工资>5000的领导编号是哪个,以及他的最低工资。
/* (1).查询领导编号>102的每个领导手下的员工固定最低工资 */
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102 /* manger_id在原始表内就可以查询,所以可以在分组前WHERE */
GROUP BY manager_id;
/* 添加筛选条件:最低工资>5000 */
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
按表达式或函数分组
案例8:按员工姓名长度分组,查询每组员工个数,筛选员工个数>5的有哪些
/* (1).查询每个长度的员工个数
(2).添加筛选条件 */
SELECT COUNT(*),LENGTH(name) 名字长度
FROM employees
GROUP BY 名字长度
HAVING COUNT(*)>5;
按多个字段进行分组
案例9:查询每个部门每个工种的员工的平均工资
/* department_id,job_id顺序可以前后颠倒 */
SELECT AVG(salary),department_id,job_id
FROM emplorees
GROUP BY department_id,job_id;
添加排序
案例10:查询每个部门每个工种的员工的平均工资,并按照平均工资高低来排序
SELECT AVG(salary),department_id,job_id
FROM emplorees
GROUP BY department_id,job_id
GROUP BY AVG(salary) DESC;