mysql groupby(条件)_mysql-6-groupby

#进阶5：分组查询

/*

SELECT

FROM

WHERE

GROUP BY

ORDER BY

查询列表要求是分组函数和 group by 之后出现的字段

1、筛选条件分为两类：

数据源 位置 关键字

分组前筛选 原始表 group by前 where

分组后筛选 分组后的结果集 group by后 having

2、分组函数做条件，肯定放在having子句中

3、能用分组前筛选的优先考虑用where筛选

4、支持多个字段分组，逗号隔开没有顺序要求

5、也可以排序

*/

USE myemployees;

#案例1：查询每个部门的平均工资

SELECT department_id, avg(salary)

FROM employees

GROUP BY department_id;

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

SELECT COUNT(*), location_id

FROM departments

GROUP BY location_id;

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

SELECT department_id, AVG(salary)

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;

#复杂筛选条件，不能用 where，而应该用 having

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

SELECT count(*), department_id

FROM employees

GROUP BY department_id

HAVING count(*) > 2;

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

SELECT job_id, Max(salary)

FROM employees

WHERE commission_pct IS NOT NULL #分组前就能筛选

GROUP BY job_id

HAVING Max(salary) > 12000;

#按表达式分组

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

SELECT count(*), Length(last_name)

FROM employees

GROUP BY Length(last_name)

HAVING count(*) > 5;

SELECT count(*) AS c, Length(last_name) AS len_name

FROM employees

GROUP BY len_name

HAVING c > 5;

#按多个字段分组

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

SELECT Avg(salary), department_id, job_id

FROM employees

GROUP BY department_id, job_id;

SELECT Avg(salary), department_id, job_id

FROM employees

WHERE department_id IS NOT NULL

GROUP BY department_id, job_id

HAVING Avg(salary) > 10000

ORDER BY Avg(salary) DESC;