mysql 分组查询 非列表字段_MY SQL 学习笔记分组查询（一）

今日心情：没有心情排版的一天

# 分组查询

/*语法：select 分组函数，列(要求出现在group by 后面的)from 表【where 筛选条件】group by 分组列表【order by 子句】 注意：查询列表必须是分组函数和groupby 后面出现的字段 */#引入：查询每个部门的平均工资 SELECT * FROM employees; 

SELECT department_id,       AVG(salary) FROM employeesGROUP BY department_id;

#查询每个工种的最高工资

SELECT MAX(salary),        job_id FROM employeesGROUP BY job_id;

#案例2：查询每个位置上的部门个数

SELECT COUNT(department_id),       location_idFROM departmentsGROUP BY location_id;

#添加筛选条件(添加分组前的筛选)

#案例1：查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary),       email,       department_idFROM employeesWHERE email LIKE '%a%'GROUP BY department_id;

#案例2：查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),       manager_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY manager_id;

#添加复杂的筛选条件(添加分组后的筛选)

#案例1：查询哪个部门的员工个数>2

/*分解 1.查询每个部门的员工个数      2.根据1的结果筛选,查询哪个部门的员工人数>2*/

select count(employee_id),department_idfrom employeeswhere count(employee_id)>2group by department_id;/*显然，这段代码运行时是错误的，这就引出了我们今天要说的重点where 与 having 的区别*/

SELECT COUNT(employee_id),department_idFROM employeesGROUP BY department_idHAVING COUNT(employee_id)>2;

#案例2：查询每个工种有奖金的最高工资大于12000的公众编号和最高工资

以下写法都可以

SELECT MAX(salary),       job_id,       commission_pctFROM employeesGROUP BY job_idHAVING MAX(salary)>12000 AND commission_pct IS NOT NULL;

SELECT MAX(salary),job_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING MAX(salary)>12000;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资员工编号以下两种写法都可以

SELECT manager_id,employee_id,MIN(salary)FROM  employeesGROUP BY manager_idHAVING manager_id>102 AND MIN(salary)>5000;

SELECT manager_id,employee_id,MIN(salary)FROM  employeesWHERE manager_id>102GROUP BY manager_idHAVING MIN(salary)>5000;