1. 简单分组查询
1):查询每个部门的平均工资
ELECT MAX(salary), job_id FROM employees GROUP BY job_id;
2):查询每个位置上的部门数
SELECT count(*), location_id FROM departments GROUP BY location_id;
2.具有筛选条件的分组查询
1):查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE ‘%a%’ GROUP BY department_id;
2):查询有奖金的每个领导手下员工高工资
SELECT MAX(salary), manage_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manage_id;
3.复杂条件的分组查询
1):查询哪个部门的员工个数大于2
查询每个部门的员工个数 – 对上面的结果进行筛选出满足条件的部门
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;
2):查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
查询每个工种的最高工资 – 筛选出最高过工资>12000
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000;
3):查询领导编号>102的每个领导手下员工的最低工资>1500的领导编号,以及其最低工资
查询每个领导手下的员工的最低工资编号大于102—>最低工资大于15000
SELECT MIN(salary),manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 15000;
分组查询的特点:
分组前筛选的数据源为原始表,关键字Where在Group by 前面
分组后筛选的数据源为分组后的结果集,关键字having在Group by 后面
注意:分组函数作为筛选条件是一定放having子句后面
4.按照表达式或函数分组查询
1):按员工姓名的长度分组,查询每一组员工个数筛选出个数大于5的有哪些
SELECT COUNT(*) 个数,LENGTH(last_name) 姓名长度
FROM emPloyees GROUP BY 3 HAVING 个数 >5;
5.按照多个字段进行分查询
1):查询每个部门每个工种的员工的平均工资(注意:这里的department_id和job_id的顺序是无先后的)
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;
2):查询每个部门每个工种的员工的平均工资;按照平均工资的高低显示
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;
ORDER BY AVG(salary) DESC
3):查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >= 6000;
4):查询所有部门的编号,员工数量和工资平均值,并按照平均工资降序排序
SELECT department_id,COUNT(*),AVG(salary) FROM employees ORDER BY AVG(salary) DESC;
5):查询具有各个job_id的员工人数
SELECT COUNT(*),job_id FROM employees GROUP BY job_id;
客官点个赞呗! (0)