Mysql学习——分组查询

## 分组查询

/*
	
	select col_groupby, 分组函数
	from 表
	【where 子句】
	group by col_groupby
	【order by 子句】

	对于原数据的要求使用where语句限制
	对于分组的要求使用having语句限制
	一般select后面的字段名必须是用于分组的字段名，不然没有意义
	order by 放到最后
	都可以用别名

*/

# 查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id

# 查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id

# 查询每个部门中，邮箱中包含‘a'的员工的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

# 查询每个有奖金的领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id



## having子句，限定group后的每个分组

# 查询哪个部门的员工的个数大于2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2

# 查询每个工种有奖金的员工中最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000

# 查询领导编号>102的每个领导手下的最低工资>5000的领导编号及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000

# 按表达式或函数分组

# 按员工姓名的长度分组，查询员工个数>5的每一组的员工个数
SELECT COUNT(*) c, LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING c>5
ORDER BY c