mysql子查询
宋时强 2014-02-07 01:21:33 Table EMPLOYEES Structure:
EMPLOYEE_ID NUMBER Primary Key,
FIRST_NAME VARCHAR2(25),
LAST_NAME VARCHAR2(25),
Salary number(8,2),
HiredDate DATE,
Departmentid number(2)
TableDepartments Structure:
Departmentid number(2) Primary Key,
DepartmentName VARCHAR2(25).
基于上述EMPLOYEES表写出查询:查出部门平均工资大于1800元的部门的所有员工,列出这些员工的全部个人信息。
mysql>select id,name,salary,deptid did from employee1 where (select avg(salary)
from employee1 where deptid = did) > 1800;
上面的查询我第一次没想出来,我自己想的是这样的
mysql>select id,name,salary,deptid did from employee1 where deptid in(select deptid,
avg(salary) from employee1 group by deptid having avg(salary)>1800)
请高手指教上面二种方式之间的区别,总感觉方法一怪怪的,分析下两者之间性能上的差别