部分函数说明：
sum为求和函数，将要求和的列sum(列名)
avg为求平均值函数，将要求平均值的列avg(列名)
nvl为如果为空则置空值为其他数据的函数，nvl(为空的列,将空值置成的其他值)
round为四舍五入函数，round(列名,保留小数位数)
1.先创建表
create table employee(id int primary key auto_increment,name varchar(50),salary bigint,depid int);
show tables;
desc employee;
//插入员工信息
insert into employee values(null,"zhangsan",15000,1);
select * from employee;
insert into employee values(null,"lisi",13000,2),(NULL,"wangwu",16000,1),(null,"linsa",14000,2);
//查看员工平均薪资，按部门分组
select AVG(salary) as avg from employee group by depid;2.查询信息
//查看薪资大于平均薪资的部门号以及员工数
select a.`depid`,count(*) from employee as a ,
(select depid,avg(salary) as salaryavg from employee group by depid) as b
where a.`depid`=b.`depid` and a.`salary`>b.`salaryavg` group by a.`depid` order by a.`depid`;
//查看薪资大于平均薪资的员工信息（姓名、薪资、部门号）
select a.`name`,a.`salary`,a.`depid` from employee a,
(select depid,avg(salary) avgsalary from employee group by depid) b
where a.`depid`=b.`depid` and a.salary > b.avgsalary;
转载自原文链接, 如需删除请联系管理员。
原文链接：查看部门里工资大于平均水平的员工信息，并按部门分组，转载请注明来源！