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

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;