查询入职最早和最晚的员工名称及入职时间
- SQL code
/*1.1*/select e.ename,e.hiredate from emp ewhere e.hiredate=(select min(hiredate) from emp)union allselect e.ename,e.hiredate from emp ewhere e.hiredate=(select max(hiredate) from emp);
【点评】:结果数据正确,但同一张表请求了四次,效率需提升,再考虑下
修改:union all改为in
- SQL code
select e.ename,e.hiredate from emp ewhere 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)