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;