日期:2014-05-17  浏览次数:20816 次

跪求!!!跪求!!!优化
查询入职最早和最晚的员工名称及入职时间
SQL code
/*1.1*/

select e.ename,e.hiredate 

from emp e

where e.hiredate=(select min(hiredate) from emp)

union all

select e.ename,e.hiredate 

from emp e

where e.hiredate=(select max(hiredate) from emp);



 

【点评】:结果数据正确,但同一张表请求了四次,效率需提升,再考虑下

修改:union all改为in
SQL code

select e.ename,e.hiredate 

from emp e

where e.hiredate in ((select min(hiredate) from emp),(select max(hiredate) from emp));





【二次点评】:结果数据正确,但还是请求了3次,在效率上还有提升空间




跪求优化


------解决方案--------------------
SQL code

SELECT ENAME, T.HIREDATE
  FROM EMP T
 WHERE NOT EXISTS (SELECT 1 FROM EMP T1 WHERE T1.HIREDATE < T.HIREDATE)
    OR NOT EXISTS
 (SELECT 1 FROM EMP T1 WHERE T1.HIREDATE > T.HIREDATE)