首页 » 技术分享 » 查看部门里工资大于平均水平的员工信息,并按部门分组

查看部门里工资大于平均水平的员工信息,并按部门分组

 

部分函数说明:

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;


转载自原文链接, 如需删除请联系管理员。

原文链接:查看部门里工资大于平均水平的员工信息,并按部门分组,转载请注明来源!

0