mysql 子查询问题
题目是找出在公司里最高工资的员工所在的部门里的所有员工(有点绕),我想了两个query:
1.select e.fname, e.lname from employee e where e.dno in (select d.dnumber from department d where e.dno = d.dnumber and e.salary in (select max(e1.salary) from employee e1));
这个只能检索出工资最高的那个员工,而这个部门里还有一个员工就是检索不出来。
2.select e.lname, e.fname from employee e where e.salary > all (select e1.salary from employee e1 where e1.dno in (select d.dnumber from department d where e1.dno = d.dnumber));
这个检索结果是empty.
想了很久,还是不行,求帮助,先谢了!
------解决方案--------------------select * from employee where dno in (select dno from employee where salary=(select max(salary ) from empploee)