创建表:
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 |
+------+----------+
请高手指教
解决方案 »
- msql数据统记
- 大家都进来看下,
- 插入1000条数据要比MSSql慢一倍多??
- 像这样的情况为什么不会ROLLBACK
- 关于mysql数据库和information_schema数据库可以删除吗?
- 读锁时,可以写入吗?如果可以写入,那么读锁定有什么意义呢?
- 为什么在SQL下面能行,在JSP下显示不了中文?大家帮我看看程序
- mysql连接数据库出现Communication failure during handshake. Is there a server running on 127.0.0.1:3306?
- update 更新时需要查询本表,sql语句怎么写
- 如果同一时间两个请求修改同一个数据会出现什么情况呢?
- SQL2005+JSP+TOMCAT 无法创建下列URL的连接,求高手帮忙!!!
- 大家看看的BS ERP 做的如何,请大家给点意见
ry) from employee921 group by deptid having deptid =a.deptid) group by tid;时输出结果为空集
having用法错误了.having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
+----+------+--------+--------+
| id | name | salary | deptid |
+----+------+--------+--------+
| 1 | zs | 1000 | 1 |
| 2 | ls | 1100 | 1 |
| 3 | ww | 1100 | 1 |
| 4 | zl | 900 | 1 |
| 5 | zl | 1000 | 2 |
| 6 | zl | 900 | 2 |
| 7 | zl | 1000 | 2 |
| 8 | zl | 1100 | 2 |
+----+------+--------+--------+
8 rows in set (0.06 sec)mysql> select a.deptid,count(*)
-> from employee921 a , (select deptid,avg(salary) as salary from employee921 group by deptid) b
-> where a.salary>b.salary
-> group by a.deptid;
+--------+----------+
| deptid | count(*) |
+--------+----------+
| 1 | 4 |
| 2 | 2 |
+--------+----------+
2 rows in set (0.05 sec)mysql>
ry) from employee921 group by deptid having deptid =a.deptid) group by tid;是错的吗?
from employee921 as a
where salary >(
select avg(salary)
from employee921
group by deptid
having deptid =a.deptid
)
group by tidhaving deptid =a.deptid是在GROUP 之后才进行的,也就是说先做
select avg(salary)
from employee921
group by deptid
然后才去判断 having deptid =a.deptid,这样结果当然不对。
mysql> select a.deptid tid from employee921 as a where salary >(select avg(salar
y) from employee921 group by deptid having deptid =a.deptid) ;
+------+
| tid |
+------+
| 1 |
| 1 |
| 2 |
+------+