显示所有雇员的姓名以及满25年服务年限的日期,我写了如下SQL语句:
select a.ename,b.hiredate from emp a left join emp b on a.ename = b.ename
and (sysdate-hiredate)/365>=25;
结果显示出14个ename记录 并且14个ename的旁边也同时显示了14个hiredate,而实际满足25年的只有12个。
我就稍微改了一下:
select a.ename,b.hiredate from emp a left join emp b on a.ename = b.ename
and empno = 7788;
结果显示出14个ename,其中只有一个empno为7788的员工显示了hiredate,这是为什么呢?我要怎么写SQL才可以满足要求呢?
select a.ename,b.hiredate from emp a left join emp b on a.ename = b.ename
and (sysdate-hiredate)/365>=25;
结果显示出14个ename记录 并且14个ename的旁边也同时显示了14个hiredate,而实际满足25年的只有12个。
我就稍微改了一下:
select a.ename,b.hiredate from emp a left join emp b on a.ename = b.ename
and empno = 7788;
结果显示出14个ename,其中只有一个empno为7788的员工显示了hiredate,这是为什么呢?我要怎么写SQL才可以满足要求呢?
select a.ename,b.hiredate from emp a left join emp b on a.ename = b.ename
and (sysdate-b.hiredate)/365>=25;select a.ename,b.hiredate from emp a left join emp b on a.ename = b.ename
and b.empno = 7788;
and (sysdate-b.hiredate)/365>=25;
加个外关联看能不能满足你的要求!
select a.ename, b.hiredate from emp a where (sysdate - a.hiredate) / 365 >= 25;
直接上面那么写不就行了吗
SELECT S.EMPNO,
S.ENAME,
CASE
WHEN (SYSDATE - S.HIREDATE) / 365 >= 25 THEN
HIREDATE
ELSE
NULL
END hiredate
FROM SCOTT.EMP S;
select a.ename,a.hiredate
from emp a
where(sysdate-a.hiredate)/365>=25;
找到答案了,还有CASE啊,嘿嘿,我还没学到CASE,谢谢:)