Mysql基础(5)——分组查询

分组查询

引入：查询每个部门的平均工资

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

案例1：查询每个工种的最高工资

SELECT MAX(salary) job_id FROM employees 
GROUP BY job_id;

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

SELECT count(*) location_id FROM departments GROUP BY location_id 

添加筛选条件

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

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

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

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id

添加复杂的筛选条件

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

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资；

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

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号以及最低工资

SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
WHERE manager_id>102 
HAVING MIN(salary)>5000;

当原表中有需要查询的数据条件时，如：领导编号，在原表有，则使用where manager_id>102 ;原表中没有而是通过查询后得出，则使用HAVING

按表达式或函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？

SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

按多个字段进行分组

案例：查询每个部门每个工种的员工的平均工资

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

添加排序

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示，按均工资降序且平均工资要>10000

SELECT AVG(salary),job_id,department_id
FROM employees
WHERE derpartment_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>100000
ORDER BY AVG(salary) DESC;

GROUP BY ;HAVING ;ORDER BY 都支持使用别名

例题

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

SELECT department_id,count(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

SELECT COUNT(*),job_id
FROM employees
WHERE job_id IS NOT NULL
GROUP BY job_id