SQL> select ename,hiredate from emp;
ENAME HIREDATE
---------- ---------------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
MARTIN 28-SEP-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
SCOTT 19-APR-87
KING 17-NOV-81
TURNER 08-SEP-81
ADAMS 23-MAY-87
ENAME HIREDATE
---------- ---------------
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
14 rows selected.
现在我们想知道WARD和JONES两名员工的HIREDATE之间相差多少个工作日。
SQL> select sum(case when to_char(jones_hd+t100.id-1,'DY') in ('Sat','Sun') then
0 else 1 end) as days
2 from
3 (
4 select
5 max(case when ename='WARD' then hiredate end) as ward_hd,
6 max(case when ename='JONES' then hiredate end) as jones_hd
7 from emp
8 where ename in('WARD','JONES')
9 ) x,t100
10 where t100.id<=jones_hd-ward_hd+1;
DAYS
----------
40
可能有人会想为什么这里要加个max()函数,其实目的就是为了去除NULL,下面做两个查询看看。
SQL> select
2 (case when ename='WARD' then hiredate end) as ward_hd,
3 (case when ename='JONES' then hiredate end) as jones_hd
4 from emp
5 where ename in('WARD','JONES');
WARD_HD JONES_H