mysql group by分组查询需求例子

 

需求:按照职位分组, 求出每个职位的最高和最低工资
select job,max(sal),min(sal) from emp group by job

 

需求:查询出每一个部门员工的平均工资
select deptno, avg(sal) from emp group by deptno

 

需求:查询出各个部门和岗位的平均工资
select deptno,job, avg(sal) from emp group by deptno,job

 

需求:查询平均工资高于2000的部门和其平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000

 

需求:查询在80,81,82,83年各进公司多少人
select year(hiredate),count(*) from emp where year(hiredate) in ('1980','1981','1982','1983') group by year(hiredate)

 

需求:查询各个管理人员下员工的平均工资, 其中平均不能低于1300, 不计算老板
select mgr,avg(sal) from emp where mgr is not null group by mgr having avg(sal) >= 1300

 

需求:查询员工最高工资和最低工资的差距
select max(salary) - min(salary) diffrence from employees

 

需求:查询各job_id的员工工资的最大值，最小值，平均值，总和。并按job_id排序
select max(salary), min(salary), avg(salary), sum(salary), job_id from employees group by job_id group by job_id