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

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;