创建表:
mysql> create table employee921(id int primary key auto_increment,name varchar(5
0),salary bigint,deptid int);插入实验数据:
mysql> 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);当我输入mysql> select a.deptid tid,count(*) from employee921 as a where salary >(select avg(sala
ry) from employee921 group by deptid having deptid =a.deptid) group by tid;时输出结果为空集
当我输入mysql> select a.deptid tid,count(*) from employee921 as a where salary >(select avg(sala
ry) from employee921 where deptid =a.deptid) group by a.deptid;时输出结果为
+------+----------+
| tid | count(*) |
+------+----------+
| 1 | 2 |
| 2 | 1 |
+------+----------+
请高手指教
mysql> create table employee921(id int primary key auto_increment,name varchar(5
0),salary bigint,deptid int);插入实验数据:
mysql> 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);当我输入mysql> select a.deptid tid,count(*) from employee921 as a where salary >(select avg(sala
ry) from employee921 group by deptid having deptid =a.deptid) group by tid;时输出结果为空集
当我输入mysql> select a.deptid tid,count(*) from employee921 as a where salary >(select avg(sala
ry) from employee921 where deptid =a.deptid) group by a.deptid;时输出结果为
+------+----------+
| tid | count(*) |
+------+----------+
| 1 | 2 |
| 2 | 1 |
+------+----------+
请高手指教
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货