列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

1.创建表：

 create table employee(
    id int primary key auto_increment,
    name varchar(5 0),
    salary bigint,
    deptid int
);

2.插入实验数据：

 insert into employee921 values(null,'zs',1000,1),
(null,'ls',1100,1),(null ,'ww',1100,1),
(null,'zl',900,1) ,(null,'zl',1000,2), 
(null,'zl',900,2) ,(null,'zl',1000,2),
 (null,'zl',1100,2);

3.sql实现语句

SELECT COUNT(*) coun,a.deptid FROM employee921 a,
(SELECT employee921.deptid,AVG(salary) avgsal FROM employee921 GROUP BY deptid ) b 
WHERE a.deptid=b.deptid AND a.salary>b.avgsal GROUP BY a.deptid;